Search code examples
symfonydoctrine-ormdql

Doctrine ManyToMany Query


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);

Solution

  • 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.