Filter model with HasMany relationship

2
votes

Let's say I have two models: Park and Items. Each Park can have a number of Items, through a HasMany relationship.

In Items table there's a park_id field and a type flag: an Item can be a fountain, or a basketball court, or whatever.

What I need to do is to filter the parks to obtain only those who has ALL of the item types passed in an array. This is what I have:

$parks = Park::whereHas('items', function($q) use ($request) {
            $q->where('type', json_decode($request->type_list));
        })->get();

But it's not working properly. Any ideas?

Thanks a lot, and happy new year to all!

Edit: I've found a working though really ugly solution:

$types_array = json_decode($request->type_list, true);

$results = [];

// A collection of parks for each item type
foreach($types_array as $type) {
    $results[] = Park::whereHas('items', function($q) use ($type) {
        $q->where('type', $type);
    })->get();
}

// Intersect all collections
$parks = $results[0];
array_shift($results);

foreach ($results as $collection) {
    $parks = $collection->intersect($parks);
}

return $parks;
3
laraveleloquenthas-many
Few question: 1) What version of Laravel are you using? 2) What is the output of dd( json_decode($request->type_list));? 3) Please can you explain how it's not working properly? - Rwd
Hi Ross, sorry for the lack of info. 1) 5.4 2) ["ag","zd"] (this is working fine) 3) I'm not getting a list of parks with ALL of the item types, but a list of parks with SOME of the item types. I need a collection of parks satisfying all the conditions. - Oscar

3 Answers

1
votes

You can use a foreach in whereHas method. It should be something like this:

$array = json_decode($request->type_list, true)
$parks = Park::whereHas('items', function($q) use ($array) {
             foreach ($array as $key => $type) {
                 $q->where('type', $type);
             }
         })->get();
0
votes

I think using where clause to match all item's type for the same row will result nothing, it's like doing where id = 1 and id = 2 and this is impossible because id can take only one value, what you should do is to use whereIn to get all items that match at least one type and then use groupBy to group result by park_id, then in the having clause you can check if the group count equal the type_list count:

$types = json_decode($request->type_list, true);

$parks = Park::whereHas('items', function($q) use ($types) {

    $q->select('park_id', DB::raw('COUNT(park_id)'))
      ->whereIn('type', $types)
      ->groupBy('park_id')
      ->havingRaw('COUNT(park_id) = ?', [ count($types) ]);

})->get();
0
votes

You can add multiple whereHas() constraint to one query:

$types_array = json_decode($request->type_list, true);
$query = Park::query();
foreach($types_array as $type) {
    $query->whereHas('items', function($q) use($type) {
        $q->where('type', $type);
    });
}
$parks = $query->get();