Doctrine2 manyToMany Inverse QueryBuilder

Question

I have two entities

Productand Category

Product has a manyToMany (unidirectional) field with Category with a jointable named product_has_category

I can find easily which Category has any Productbut I don't know how to get the inverse...

Let's say I want to get all Product for a certain Category in a form, that form's field is category so I can't get an entity field type with Product query, otherwise it will fail.

$form
    ->add('category', 'entity', array(
        'class'     => 'AppBundle\Entity\Category',
        'property'  => 'name',
        'query_builder' => function(EntityRepository $em) use ($product) {
            return $em
                ->createQueryBuilder('c')
                ->innerJoin('AppBundle\Entity\Product', 'p', 'WITH', 'p.id = :product_id')
                ->setParameter('product_id', $product->getId())
                ;
        },
        'multiple'  => true
    ))
;

Of course this doesn't work, but you get the idea of what I want to do. As Category doesn't have any mapped field with Product (because it is an unidirectional mapping), I can't find easy thoose products...

The middle table doesn't has an entity, because we don't need it, it's just a mysql table.

Running a MySQL query would be this easy, but we need it to be inside a QueryBuilder because we will use it in forms.

SELECT c.name 
FROM category c
INNER JOIN product_has_category pc ON pc.category_id = e.id
INNER JOIN product p ON pc.product_id = p.id
WHERE p.id = 10
;

This, of course returns me exactly what I want, because on MySQL I do have access to the middle table so I can join everything.. but, QueryBuilder? I don't know

I've tried this but I just got everything freezed (over 7M records fetched I guess)

$em
->createQueryBuilder('c')
->select('c')
->innerJoin('AppBundle\Entity\Product', 'p', 'WITH', '1 = 1')
->innerJoin('p.categories', 'c')
->where('p.id = :product_id')
->setParameter('product_id', $product->getId())
;

I think it is not duplicated because other question doesn't include a QueryBuilder solution/question, and some people may be confused about how to do so.

You can use "arbitrary join" http://stackoverflow.com/a/15444719 — jkavalik, Oct 26 '15 at 12:50
@jkavalik But how can I do that in a QueryBuilder? Because that is a raw query. — Robert W. Hunter, Oct 26 '15 at 13:47
In your code `p.id = :product_id` should be in `where()`, the `WITH` keyword in DQL is the equivalent of the `ON` from SQL join. Otherwise the QB is just a tool to help you build the query dynamically but it uses all the same features and syntax. You can just take Ocramius' second example and rewrite it directly (or test it with dql beforehand - imho you should only use QB for dynamic queries, anything else is much shorter and more readable as DQL) — jkavalik, Oct 26 '15 at 13:56
I did that and I just got my computer freezed because I have over 7 million products, and everything exploded... This is my QB (see updated question) — Robert W. Hunter, Oct 26 '15 at 14:02
you are using `c` as alias twice and missing the part of the `where` condition which joins `c1` with `c2`. When doing such things, use $qb->getSQL() to see the query it creates before you run it (it has getDQL() method too to check that one). — jkavalik, Oct 26 '15 at 14:05
Okay thanks, that seems to work. Please create an answer in order to approve it if you want, otherwise I will put my code as answer in case someone needs the same. — Robert W. Hunter, Oct 26 '15 at 14:18
Possible duplicate of [Doctrine 2 DQL - how to select inverse side of unidirectional many-to-many query?](http://stackoverflow.com/questions/5432404/doctrine-2-dql-how-to-select-inverse-side-of-unidirectional-many-to-many-query) — jkavalik, Oct 26 '15 at 14:34

score 2 · Accepted Answer · edited May 23 '17 at 11:53

For thoose who ants to know how to do it inside QueryBuilder, thanks to @jkavalik answer referencing other question

Doctrine 2 DQL - how to select inverse side of unidirectional many-to-many query?

This is the code I've used which is working like a charm.

$form
    ->add('categories', 'entity', array(
        'class'     => 'AppBundle\Entity\Category',
        'property'  => 'name',
        'query_builder' => function(EntityRepository $em) use ($product) {
            return $em
                ->createQueryBuilder('c1')
                ->innerJoin('AppBundle\Entity\Product', 'p', 'WITH', '1 = 1')
                ->innerJoin('p.equipments', 'c2')
                ->where('p.id = :product_id')
                ->andWhere('c1.id = c2.id')
                ->setParameter('product_id', $product->getId())
            ;
        }
    ))
;

Doctrine2 manyToMany Inverse QueryBuilder

1 Answers1