17

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
jedrzej.kurylo
  • 39,591
  • 9
  • 98
  • 107
Karl
  • 5,435
  • 11
  • 44
  • 70
  • A bit of a dirty solution would be using 2 where not in statements, 1 for both categories. – milo526 May 06 '17 at 14:15
  • Yeah I thought that but I literally have about 100 categories to filter by, guess I could always foreach? – Karl May 06 '17 at 14:16
  • Acually, even a where would return the same as whereNotIn, would it not? – Karl May 06 '17 at 14:17
  • mm you can try dump the generated query: `\DB::enableQueryLog(); dd(\DB::getQueryLog());` tho. – Chay22 May 06 '17 at 14:22
  • 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 – Karl May 06 '17 at 14:31

1 Answers1

20

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();
jedrzej.kurylo
  • 39,591
  • 9
  • 98
  • 107
  • Unfortunately this is still returning the same results :/ – Karl May 06 '17 at 16:26
  • 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. – jedrzej.kurylo May 06 '17 at 16:35
  • 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. – Karl May 06 '17 at 18:44
  • Whats the query youre getting? – jedrzej.kurylo May 06 '17 at 19:11
  • 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 – Karl May 06 '17 at 19:28