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

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