CODEWITHSUNDEEP
mysqlsqlmany-to-manyconditional-statements
Talus
Talus

Reputation: 754

MySQL filter many elements from many-to-many relationship

I'm currently working on objects that are linked through a many-to-any relationship (it means that many objects can be linked to... not many objects of the same type, but many objects which type can vary).

But, for this question, a many-to-many will be plenty enough.

So, let's say I have a ObjectA table, with the following :

CREATE TABLE (
    id INT(11) NOT NULL, 
    label VARCHAR(30) NOT NULL,
    -- primary keys and all the stuff
);

CREATE TABLE ObjectB (
    id INT(11) NOT NULL,
    label VARCHAR(30) NOT NULL,
    -- primary keys and all the stuff
);

CREATE TABLE ObjectA_ObjectB (
    objectA_id INT(11) NOT NULL,
    objectA_type VARCHAR(250) NOT NULL, -- for the many to any
    objectB_id INT(11) NOT NULL,
    -- primary keys and all the stuff
);

Let's say I want to filter the ObjectA with 2 or three elements from ObjectB. You will think that I should use a IN :

SELECT *
    FROM ObjectA a
        LEFT JOIN ObjectA_ObjectB relation ON a.id = relation.objectA_id AND "ObjectA" = relation.objectA_class
        LEFT JOIN ObjectB b ON relation.objectB_id = b.id
    WHERE b.id IN (1, 2, 3);

But, the thing is, with this request, it gets all the objectAs that are linked with at least ONE of the ObjectBs searched (here, 1, 2, and 3). But, what I want, are those that have all the ObjectB's 1, 2 and 3.

I already tried some things (like the ALL possibility, or pondering to make a first filter outside of SQL), but it didn't give the expected results.

Any ideas ?

To sum up, I'd like to be able to do a bit like the issues from GitHub and how they are filtering their label. If you pick up a bunch of labels, only the issues that have all the selected labels are returned, rather than all the issues that have at least one label.

Thanks !

Upvotes: 1

Views: 1726

Answers (2)

Arth
Arth

Reputation: 13110

From your comments it sounds like you don't want LEFT JOIN which will return ALL ObjectAs which match the WHERE clause. As for the relation condition you have to count the matching rows: 

  SELECT *
    FROM ObjectA a
    JOIN ObjectA_ObjectB relation 
      ON a.id = relation.objectA_id 
     AND "ObjectA" = relation.objectA_class
    JOIN ObjectB b 
      ON relation.objectB_id = b.id
GROUP BY a.id
  HAVING COUNT(CASE WHEN b.id IN (1,2,3) THEN 1 ELSE NULL END)=3;

OR:

  SELECT *
    FROM ObjectA a
    JOIN ObjectA_ObjectB relation 
      ON a.id = relation.objectA_id 
     AND "ObjectA" = relation.objectA_class
    JOIN ObjectB b 
      ON relation.objectB_id = b.id
     AND b.id IN (1,2,3)
GROUP BY a.id
  HAVING COUNT(*)=3;

If you don't actually need the ObjectB data in this query you can simplify this even further:

  SELECT a.*
    FROM ObjectA a
    JOIN ObjectA_ObjectB relation 
      ON a.id = relation.objectA_id 
     AND "ObjectA" = relation.objectA_class
     AND relation.objectB_id IN (1,2,3)
GROUP BY a.id
  HAVING COUNT(*)=3;

This assumes that you can only have one of each ObjectB.id linked to an ObjectA through the pivot table.

Upvotes: 1

Alepac
Alepac

Reputation: 1831

A solution is to count the matching conditions in an having clause.

SELECT *
FROM ObjectA a
    JOIN ObjectA_ObjectB relation ON a.id = relation.objectA_id AND "ObjectA" = relation.objectA_class
    JOIN ObjectB b ON relation.objectB_id = b.id
WHERE b.id IN (1, 2, 3) GROUP BY a.id HAVING COUNT(b.id)=3;

See sqlfidle example.

Upvotes: 1

Related Questions