vendev's avatar

Filter by column in related table not working?

Hi there,

I'm new to Laravel and can't figure out how to filter result by column in the related table. I have many to many relation with the following table structure:

categories' -> id, name

items -> id, name, active(bool)

category_item -> category_id, item_id

My goal is to retrieve categories with only active items (active = 1) From the controller i'm doing something like this:

public function index(): CategoryCollection
{
    $categories = Category::with('items')
        ->whereHas('items', fn ($query) => $query->where('active', 1));
    return new CategoryCollection($categories);
}

And I get the categories with items in it, but also with inactive ones:

{ "data": [ { "id": 70, "name": "autem", "items": [ { "id": 3, "name": "Item 1", "active": 0, }, { "id": 4, "name": "Item 2", "active": 1, } ] } ] }

I'm using Laravel 10, php 8.2

0 likes
5 replies
jaseofspades88's avatar

If a category can have active and inactive items, filtering only on the active being true will not be enough in the case where a category has items of both. Consider changing the condition to ->where('active', '!=', false). This will exclude any inactive items.

Couple of additional points...

public function newEloquentBuilder($query): CategoryCollection
{
    return new CategoryCollection($query);
}

...putting this on your Category model will cast any queries on Category to the collection you're manually instantiating here.

If you cast your active to a bool also, you can explicitly pass true/false to the where function. Probably can do it regardless as boolean would be cast to a numerical equivalent anyway

vendev's avatar

@jaseofspades88 Thanks for the advice. Unfortunately this "->where('active', '!=', false)" did not work.

Nakov's avatar
Nakov
2 years ago
Best Answer
Level 73

What about combining the two:

$categories = Category::withWhereHas('items', fn ($query) => $query->where('active', 1));

and make sure that there is no other query happening within the CategoryCollection, but it actually uses the items you are passing through the constructor :) because I cannot see what you have there.

1 like
Nakov's avatar

@vendev you can mark the answer as "Best Answer" if it worked :)

1 like

Please or to participate in this conversation.