Doctrine2 query with select on multiple entities from different Symfony2 bundles

Question

Hello I have three Doctrine2 entities in my Symfony2 application: two ("Promo" and "PromoPeriod") in the bundle Acme:PromoBundle; one ("Shop") in the bundle Acme:ShopBundle. The relationships are: Promo - PromoPeriod: Many-to-One. PromoPeriod - Shop: One-to-Many.

In the repository of the entity "Promo", I try to get all the promo and shops as follows:

    return $this->getEntityManager()
        ->createQuery("SELECT p, s
            FROM AcmePromoBundle:Promo p JOIN p.period pp JOIN pp.shops s")
            ->getResult();

where: p.period is the relationship between the Promo instance p and the PromoPeriod period; pp.shops is the relationship between the PromoPeriod pp and the Shops. The following error is returned:

An exception has been thrown during the rendering of a template 
("The parent object of entity result with alias 's' was not found. 
The parent alias is 'pp'.")

So, I argued that Doctrine is not capable of understanding the type of "s" (which is a Shop entity), since it is located in another bundle. Hence I tried to add an INSTANCE OF clause:

SELECT p, s
FROM AcmePromoBundle:Promo p JOIN p.period pp JOIN pp.shops s
WHERE s INSTANCE OF AcmeShopBundle:Shop

And again nothing. Perhaps this is not the way to solve the problem.

Any idea?

Answer 1

You should select the 3 entities, like so

SELECT p, pp, s
FROM AcmePromoBundle:Promo p
JOIN p.period pp
JOIN pp.shops s

You're doing a fetch join, ie: doctrine will return the entities of the root of the query (here Promo(s)), and hydrate it with the additional select entities. So if you tell doctrine to load the shops, that "come" from period, but to not select the period ... he can't do what you're asking it to do. Here, the SQL query goes fine, it's when doctrine tries to hydrate objects that it throws an exception.

Read the related documentation http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#joins