Doctrine query builder error on join

Question

How do I write the following with doctrine query builder?

SELECT
    registry.id ,
    registry.couple_id,
    registry.gift_id,
    Sum(purchase.amount) as totalContribution,
    gift.title,
    gift.price
FROM
    gift,
    registry
LEFT JOIN
    purchase ON purchase.registry_id = registry.id
WHERE
    registry.gift_id = gift.id
    AND registry.couple_id = 1 
GROUP BY
    registry.couple_id,
    registry.gift_id

I tried:

$qb = $this->createQueryBuilder('g') //gift
    ->from('\BBB\GiftBundle\Entity\Registry', 'reg')
    ->select('g.id , g.title, g.description, g.price, g.imageMedium')
    ->addSelect('SUM(p.amount) as totalContribute')
    ->leftJoin('\BBB\GiftBundle\Entity\Purchase', 'p', 'ON','reg.id = p.registry')
    ->where('reg.gift = g.id')
    ->AndWhere('reg.couple = :coupleID')
    ->orderBy('reg.id','DESC')
    ->groupBy('reg.couple')
    ->groupBy('reg.gift')
    ->setParameter('coupleID', $coupleID);

But it gives me following error:

[Semantical Error] line 0, col 178 near 'p ON reg.id =': Error: Identification Variable BBB\GiftBundle\Entity\Purchase used in join path expression but was not defined before.

noetix noetix · Accepted Answer · 2012-10-21T00:28:08

DQL can only join tables relating to the current entity (that being Registry in this DQL). Your Registry entity will need to have a declared OneToMany relation.

Assuming that this relation was called purchases you would join like so:

->leftJoin('reg.purchases', 'p')

Doctrine query builder error on join

1 Answers