SQL How to Search a Many to Many Relationship

SQL how to search a many to many relationship

To obtain the details of notes that have both labels 'One' and 'Two':

select * from notes
where note_id in
( select note_id from labels where label = 'One'
  intersect
  select note_id from labels where label = 'Two'
)

With a many-to-many relationship, search by the many for the one

You can use aggregation:

select release_id
from release_artifacts
group by release_id
having sum( artifact_id in ('A1', 'A2', 'A3') ) = 3 and
       count(*) = 3;

This assumes no duplicates.

Or you can use string or array aggregation:

select release_id
from release_artifacts
group by release_id
having string_agg(artifact_id order by artifact_id) = 'A1,A2,A3';

How do I use SQL to search a many to many relationship using AND

You are looking for movies for which exist both tags 1 and 7. We don't use joins usually when we only want to check whether data exists. We use EXISTS. Or IN, which expresses the same thing (movies that are in the set of tag 1 movies and also in the set of tag 7 movies).

The idea is that we select FROM the table we want to see results from. And we use the WHERE clause to tell the DBMS which rows we want to see.

With EXISTS

SELECT m.moviename
FROM tblmovies m
WHERE EXISTS (SELECT null FROM tblbridge b WHERE b.tagid = 1 AND b.movieid = m.movieid)
  AND EXISTS (SELECT null FROM tblbridge b WHERE b.tagid = 7 AND b.movieid = m.movieid)
ORDER BY m.moviename;

With IN

SELECT m.moviename
FROM tblmovies m
WHERE m.movieid IN (SELECT b.movieid FROM tblbridge b WHERE b.tagid = 1)
  AND m.movieid IN (SELECT b.movieid FROM tblbridge b WHERE b.tagid = 7)
ORDER BY m.moviename;

I should add that these are not the only options available to get that result. But they are the straight-forward ones. (Another is conditional aggregation, but you'll learn this later.)

How to perform Many to Many Relationship Filter Query

Try this query:

SELECT P.EquipmentID, E.Name
FROM Pivot P 
JOIN Equipments E ON P.EquipmentID=E.Id
WHERE E.RecipeId IN (1,2,3) 
GROUP BY P.EquipmentID, E.Name
HAVING COUNT(*)=3;

1. Join Pivot and Equipments table.
2. Add condition WHERE E.RecipeId IN (1,2,3).
3. Add GROUP BY P.EquipmentID, E.Name.
4. Add HAVING COUNT(*)=3; for any group of P.EquipmentID, E.Name that occur 3 times; effectively matches your condition of "only EquipmentID that appear in Receipe 1,2,3".

Here's a fiddle

Laravel query on Many to Many relationship

Since the whereIn() method matches against any of the values provided, and not all, you'll need to modify this. Specificying a number of whereHas() clauses, 1 for each Tag, should work:

$photoQuery = Photo::query();
foreach ($request->input('tags') as $tag) {
  $photoQuery = $photoQuery->whereHas('tags', function ($query) use ($tag) {
    return $query->where('tag', $tag);
  });
}
$photos = $photoQuery->get();

Now, depending on the tags being sent to your API (assuming through the $request variable as a 'tags' => [] array), this query will include a whereHas() clause for each Tag, and only return Photo records that have all specified Tags.

---

Related Topics