Laravel belongsToMany where doesn't have one of

Question

I have two tables: categories and videos, I then have a pivot table for these as it's a belongsToMany relationship.

What I'm trying to do is get all of the videos where there isn't a single instance of the video being in one of many categories.

e.g.

• Video 1 is in category 1, 2 and 3.
• Video 2 is in category 1 and 3.
• Video 3 is in category 1.

I want to get the video which is NOT in category 2 or 3, meaning this will return Video 3.

What I've tried so far, which doesn't give the intended result, this is because another row is still found for Video 1 and 2, as they are in Category 1:

Video::whereHas('categories', function($query) {
    $query->whereNotIn('category_id', [2,3]);
})->take(25)->get();

The query populated from this is:

select * from `videos` where exists (select * from `categories` inner join 
`category_video` on `categories`.`id` = `category_video`.`category_id` where 
`videos`.`id` = `category_video`.`video_id` and `category_id` != ? and 
`category_id` != ? and `categories`.`deleted_at` is null) and `videos`.`deleted_at` 
is null order by `created_at` desc limit 25

Repeater

A bit of a dirty solution would be using 2 where not in statements, 1 for both categories. – Thomasthomasa

Yeah I thought that but I literally have about 100 categories to filter by, guess I could always foreach? – Repeater

Acually, even a where would return the same as whereNotIn, would it not? – Repeater

mm you can try dump the generated query: \DB::enableQueryLog(); dd(\DB::getQueryLog()); tho. – Aridatha

I've added the query which is generated by the call, which shows that it is always going to find that match. I'm not even sure how you'd do this with just mysql – Repeater

Answer 1

You can use Eloquent's whereDoesntHave() constraint to get what you need:

// get all Videos that don't belong to category 2 and 3
Video::whereDoesntHave('categories', function($query) {
  $query->whereIn('id', [2, 3]);
})->get();

Felicific

Unfortunately this is still returning the same results :/ – Repeater

It can't give you the same results as it's a different query, You fetched videos that have at least one category other than 2 and 3. This one gives you videos that don't have category with id 2 or 3. – Felicific

Well obviously not the exact results, but it's still returning videos which are either in category 2 or 3, as it's found in category 1. – Repeater

Whats the query youre getting? – Felicific

select * from videos where not exists (select * from categories inner join category_video on categories.id = category_video.category_id where videos.id = category_video.video_id and category_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and categories.deleted_at is null) and videos.deleted_at is null order by created_at desc limit 25 – Repeater