Doctrine ManyToMany Query

Question

I have a Product Entity which has a ManyToMany relationship with a Taxon Entity. I want to find all the products that belong to the intersection of all Taxons. For instance I want to find all products that belong to taxons with IDs 1 and 2.

Products
{1,2,3}

Taxons
{1,2,3,4,5}

ProductsToTaxons
{{p1,t1},{p1,t2}, {p2,t2}}

I want to retrieve the following set ONLY when querying products for taxons 1 and 2:

Product
{1} 
which is from {{p1,t1}, {p1,t2}}

Okay, So here is the DQL that i tried... but it doesn't work?

SELECT p FROM SRCProductBundle:Product p
JOIN p.taxons t 
WHERE t.id = 1 AND t.id = 2

(P.S. I would also do this with QueryBuilder with as well)

EDIT

To clarify, here is the SQL that I would like to translate into DQL/QueryBuilder.

select p.id 
from product p 
where exists (select product_id 
              from product_to_taxon 
              where taxon_id = 1 
              and product_id = p.id) 
and exists (select product_id 
            from product_to_taxon 
            where taxon_id = 4 
            and product_id = p.id);

How do you create the DQL? could you dump the script for creating that? I guess you want it be something like `... WHERE t.id IN (1,4)`, right? — Javad, Apr 04 '14 at 21:03
@Javad thanks for the response. That is an actual DQL I am generating. I don't want to us the 'IN' operator because that returns a set UNION and not a set INTERSECTION, so IN would return Products {1,2} — jspizziri, Apr 04 '14 at 21:11
I know what you mean; but as you see in the DQL you do selection on Product but I think you want to apply the condition clause on `JOIN ... ON (t.id = 1 OR t.id = 2)`. When you do the selection on Product and just use the join however you use `IN` it does not create UNION — Javad, Apr 04 '14 at 21:24
@Javad what does what show? The DQL is above and the query is returning an empty set. — jspizziri, Apr 07 '14 at 13:34
@Javad please checkout the SQL statement I included above. I just want to do that in DQL. — jspizziri, Apr 07 '14 at 14:09
When you run the SQL query in MySQL do you get the result which you expect? To implement `exists` in Doctrine you can follow this link [http://stackoverflow.com/questions/10030538/query-with-exists-for-doctrine-symfony2] — Javad, Apr 07 '14 at 14:14

score 4 · Answer 1 · answered Oct 15 '15 at 15:52

You can use the MEMBER OF statement to achieve this although in my experience it hasn't produced very performant results with a ManyToMany relationship: In DQL:

SELECT p FROM SRCProductBundle:Product p
WHERE 1 MEMBER OF p.taxons OR 2 MEMBER OF p.taxons

Or with Query builder

$this->createQueryBuilder('p')
    ->where(':taxon_ids MEMBER OF p.taxons')
    ->setParameter('taxon_ids', $taxonIdsArray)
    ->getQuery()
    ->getResult();

This will create SQL similar to the example provided although in my experience it still had a join in the EXISTS subqueries. Perhaps future versions of Doctrine can address this.

score 1 · Answer 2 · answered Oct 07 '16 at 20:20

I think you want something like this:

    $qb = $this
        ->createQueryBuilder('p')
        ->select('p.id')
    ;
    $qb
        ->leftJoin('p.taxons', 'taxon1', Join::WITH, 'taxon1.id = :taxonId1')
        ->setParameter('taxonId1', 1)
        ->andWhere($qb->expr()->isNotNull('taxon1'))
        ->leftJoin('p.taxons', 'taxon2', Join::WITH, 'taxon2.id = :taxonId2')
        ->setParameter('taxonId2', 2)
        ->andWhere($qb->expr()->isNotNull('taxon2'))
    ;

Which is equivalent to the SQL:

SELECT p.id
  FROM products p
  LEFT JOIN taxons t1 ON (p.id = t1.product_id AND t1.id = 1)
  LEFT JOIN taxons t2 ON (p.id = t2.product_id AND t2.id = 2)
 WHERE t1.id IS NOT NULL
   AND t2.id IS NOT NULL
;

score 0 · Answer 3 · answered Apr 04 '14 at 21:04

Your DQL has wrong logic. You can't have a taxon with both id=1 and id=4. You could do it like this:

SELECT p FROM SRCProductBundle:Product p
JOIN p.taxons t 
WHERE t.id = 1 OR t.id = 4

But I would prefer this way:

SELECT p FROM SRCProductBundle:Product p
JOIN p.taxons t 
WHERE t.id IN (1, 4)

Using query builder that would look something like this, assuming you're in EntityRepository class:

$this->createQueryBuilder('p')
    ->join('p.taxons', 't')
    ->where('t.id IN :taxon_ids')
    ->setParameter('taxon_ids', $taxonIdsArray)
    ->getQuery()
    ->getResult();

I understand that, however my question is this: I want to query the join table. So I want to find Products that have both taxon 1 AND taxon 2. Please read the set example I gave. How do I achieve that? — jspizziri, Apr 04 '14 at 21:07

score 0 · Accepted Answer · answered Apr 07 '14 at 14:51

For lack of a clean way to do this with DQL, and after a considerable amount of research, I resorted to doing this in Native SQL. Doctrine allows Native SQL via the EntityManager with createNativeQuery().

So in short, I utilized this ability and constructed the SQL query included in my question as a string and then passed it to the createNativeQuery() function.

This does appear to have some drawbacks as it appears I will be unable to use the KnpPaginatorBundle with it... So I might end up just filtering the results in PHP rather than SQL, which I'm hesitant to do as I think there are performance drawbacks.

Doctrine ManyToMany Query

4 Answers4

Linked