Filter categories on AND instead of OR basis in Laravel

user_10322381 0
Viewed 85

I would like to filter journalists on the basis of the categories they write about (e.g. Sports, Music). When the user selects multiple categories, e.g. sports and music, it should show only the journalist which are writing about Sports AND Music. Currently, I have only managed to filter on journalists who are writing about Sports AND/OR Music.

The categories selected by the user are represented in an array:

enter image description here

And the combination journalists and category combination are defined in a linking table:

enter image description here

In my current code I make use of the whereHas command, but this only looks for each of the journalist_category_ids into the array $categoriesIds and results in the selection of the OR combination: 

$journalists    = Journalist::where('active', 1)
    ->whereHas('categories', function($q) use ($categoriesIds) {
        $q->whereIn('journalist_category_id', $categoriesIds);
    })->paginate(100);
eloquent laravel filtering
1 Answers

This is not a laravel specification issue, it's about MySQL more that Laravel.

When you are selecting select * from table where id in (10, 20, 30) it's equivalent to select * from table where (id = 10 or id = 20 or id = 30) so it's a shortcut -so to speak- for a multiple OR's

to solve this you will need to play with sql [ thanks to andreas & boris for helping out ]

so grouping by journalist and then select where the journalist count is equal to the count of the categories ids will solve the issue.

$journalists    = Journalist::where('active', 1)
->whereHas('categories', function($q) use ($categoriesIds) {
    $q->select('journalist_id', \DB::raw('count(journalist_id) as jcount'));
    $q->whereIn('journalist_category_id', $categoriesIds);
    $q->groupBy('journalist_id');
    $q->having('jcount', count($categoriesIds));
})->paginate(100);
user_23596790
Related