How to get records with multiple where in many to many relationship?

Question

I'm working on a Laravel version 9 project. At first, I explain the tables and relationships. There are 3 tables:

advertisements, advertisement_tags and the pivot table advertisement_advertisement_tag. As you can see advertisements and advertisement_tags has a many-to-many relationship. Now I want to get the advertisements that only have specific ‍‍‍advertisement_tags. For example I send an array including [1,2,5]. I expect to get advertisements that have exactly advertisement_tags with 1,2 and 5 Id together, and I don't want to get advertisements that only have one of these tags 1,2 or 5. I mean I have tried whereIn clause.

If you can help me, there is no difference between pure SQL and Query Builder. thanks

Answer 1

Hi sempley you can join the tables together then do your condition after that group your result by advertisement id then have condition to check each record have all tags count

I think this sample query below can help you to get concept

$tags=[1,2,5];
DB::table('advertisements')
->join('advertisement_tags','advertisements.id','advertisement_tags.ad_id')
->whereIn('advertisement_tags.tag_id',$tags)
->select('advertisements.*',DB:raw('count(advertisement_tags.*) as tag_count '))
->GoupBy('advertisements.id')
->Having('tag_count',count($tags))->get()

also you can check laravel query Documentations