How to Filter SQL Results in a Has-Many-Through Relation

How to filter SQL results in a has-many-through relation

I was curious. And as we all know, curiosity has a reputation for killing cats.

So, which is the fastest way to skin a cat?

The cat-skinning environment for this test:

• PostgreSQL 9.0 on Debian Squeeze with decent RAM and settings.
• 6.000 students, 24.000 club memberships (data copied from a similar database with real life data.)
• Slight diversion from the naming schema in the question: student.id is student.stud_id and club.id is club.club_id here.
• I named the queries after their author in this thread.
• I ran all queries a couple of times to populate the cache, then I picked the best of 5 with EXPLAIN ANALYZE.
• Relevant indexes (should be the optimum - as long as we lack fore-knowledge which clubs will be queried):

ALTER TABLE student ADD CONSTRAINT student_pkey PRIMARY KEY(stud_id );
ALTER TABLE student_club ADD CONSTRAINT sc_pkey PRIMARY KEY(stud_id, club_id);
ALTER TABLE club       ADD CONSTRAINT club_pkey PRIMARY KEY(club_id );
CREATE INDEX sc_club_id_idx ON student_club (club_id);

club_pkey is not required by most queries here.

Primary keys implement unique indexes automatically In PostgreSQL.

The last index is to make up for this known shortcoming of multi-column indexes on PostgreSQL:

A multicolumn B-tree index can be used with query conditions that
involve any subset of the index's columns, but the index is most
efficient when there are constraints on the leading (leftmost) columns.

Results

Total runtimes from EXPLAIN ANALYZE.

1) Martin 2: 44.594 ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

2) Erwin 1: 33.217 ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING COUNT(*) > 1
   ) sc USING (stud_id);

3) Martin 1: 31.735 ms

SELECT s.stud_id, s.name
FROM   student s
WHERE  student_id IN (
   SELECT student_id
   FROM   student_club
   WHERE  club_id = 30

   INTERSECT
   SELECT stud_id
   FROM   student_club
   WHERE  club_id = 50
   );

4) Derek: 2.287 ms

SELECT s.stud_id,  s.name
FROM   student s
WHERE  s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 30)
AND    s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 50);

5) Erwin 2: 2.181 ms

SELECT s.stud_id,  s.name
FROM   student s
WHERE  EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 30)
AND    EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 50);

6) Sean: 2.043 ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club x ON s.stud_id = x.stud_id
JOIN   student_club y ON s.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50;

The last three perform pretty much the same. 4) and 5) result in the same query plan.

Late Additions

Fancy SQL, but the performance can't keep up:

7) ypercube 1: 148.649 ms

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM   club AS c 
   WHERE  c.club_id IN (30, 50)
   AND    NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

8) ypercube 2: 147.497 ms

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 30 AS club_id  
      UNION  ALL
      SELECT 50
      ) AS c
   WHERE NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

As expected, those two perform almost the same. Query plan results in table scans, the planner doesn't find a way to use the indexes here.

9) wildplasser 1: 49.849 ms

WITH RECURSIVE two AS (
   SELECT 1::int AS level
        , stud_id
   FROM   student_club sc1
   WHERE  sc1.club_id = 30
   UNION
   SELECT two.level + 1 AS level
        , sc2.stud_id
   FROM   student_club sc2
   JOIN   two USING (stud_id)
   WHERE  sc2.club_id = 50
   AND    two.level = 1
   )
SELECT s.stud_id, s.student
FROM   student s
JOIN   two USING (studid)
WHERE  two.level > 1;

Fancy SQL, decent performance for a CTE. Very exotic query plan.

10) wildplasser 2: 36.986 ms

WITH sc AS (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30,50)
   GROUP  BY stud_id
   HAVING COUNT(*) > 1
   )
SELECT s.*
FROM   student s
JOIN   sc USING (stud_id);

CTE variant of query 2). Surprisingly, it can result in a slightly different query plan with the exact same data. I found a sequential scan on student, where the subquery-variant used the index.

11) ypercube 3: 101.482 ms

Another late addition ypercube. It is positively amazing, how many ways there are.

SELECT s.stud_id, s.student
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    NOT EXISTS (
   SELECT *
   FROM  (SELECT 14 AS club_id) AS c  -- can't be excluded for missing the 2nd
   WHERE  NOT EXISTS (
      SELECT *
      FROM   student_club AS d
      WHERE  d.stud_id = sc.stud_id
      AND    d.club_id = c.club_id
      )
   );

12) erwin 3: 2.377 ms

ypercube's 11) is actually just the mind-twisting reverse approach of this simpler variant, that was also still missing. Performs almost as fast as the top cats.

SELECT s.*
FROM   student s
JOIN   student_club x USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    EXISTS (                        -- ... and membership in 2nd exists
   SELECT *
   FROM   student_club AS y
   WHERE  y.stud_id = s.stud_id
   AND    y.club_id = 14
   );

13) erwin 4: 2.375 ms

Hard to believe, but here's another, genuinely new variant. I see potential for more than two memberships, but it also ranks among the top cats with just two.

SELECT s.*
FROM   student AS s
WHERE  EXISTS (
   SELECT *
   FROM   student_club AS x
   JOIN   student_club AS y USING (stud_id)
   WHERE  x.stud_id = s.stud_id
   AND    x.club_id = 14
   AND    y.club_id = 10
   );

Dynamic number of club memberships

In other words: varying number of filters. This question asked for exactly two club memberships. But many use cases have to prepare for a varying number. See:

• Using same column multiple times in WHERE clause

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

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 to count MySQL results in a has-many-through relation

The query uses table aliases for tables student_club and club. This allows to return rows only for students who are in both clubs. Then, using COUNT allows to return the number of students:

SELECT COUNT(*) AS nb
FROM student s, student_club sc1, club c1, student_club sc2, club c2
WHERE s.id=sc1.student_id AND sc1.club_id=c1.id AND c1.name="CLUB1"
AND s.id=sc2.student_id AND sc2.club_id=c2.id AND c2.name="CLUB2"

If you really want to use the "Martin 2" query, you may count the number of records this way:

SELECT COUNT(*) AS nb
FROM (
    SELECT s.stud_id, s.name
    FROM   student s
    JOIN   student_club sc USING (stud_id)
    WHERE  sc.club_id IN (30, 50)
    GROUP  BY 1,2
    HAVING COUNT(*) > 1
) tmp;

Laravel eloquent filter on hasMany relationship

To filter the Entity order with a relation you need to use whereHas

Order::whereHas('fullOrderItems', function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            })
            ->with(['fullOrderItems' => function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            }])
            ->orderBy('dateCreated')
            ->get();

You can for example get the order filtered by branch id but get all the fullOrderItems (ingore the branch id) of those fullOrderItems like this

Order::whereHas('fullOrderItems', function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            })
            ->with('fullOrderItems')
            ->orderBy('dateCreated')
            ->get();

This last example will make it simpler to understand the difference between the two filters.

For why the with condition doesnt show on the query:

it is used on a second unique query that fetchs the relation using the ids of orders retrieved in the first query. that way you get the orders, each with their respective fullOrderItems with only two queries.

---

Related Topics