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

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

CodePudding user response：

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

CodePudding user response：

How about using having in order to be sure that you got the correct number of tags for each advertisement?

SELECT advertisment_id
FROM advertisement_advertisement_tag
WHERE tag_id in($searched_tag_ids)
GROUP BY advertisment_id
HAVING COUNT(DISTINCT tag_id)=$number_of_searched_tags

This query will return all advertisment_id's with all the tags ad then you can join this results with advertisements table to get all the info of the matching advertisement_id's.