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

Solution

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();

Comments

[Karl]

Unfortunately this is still returning the same results :/

[jedrzej.kurylo]

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.

[Karl]

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.

[jedrzej.kurylo]

Whats the query youre getting?

[Karl]

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