Doctrine query error on leftJoin

Question

I wrote this query.

$query = $this->getEntityManager()->createQueryBuilder();
    $query->select(['cart','subscriptions', 'member'])
    ->from('ComitiUserBundle:Cart', 'cart')
    ->leftJoin('cart.subscriptions', 'subscriptions')
    ->leftJoin('subscriptions.member', 'member')
    ->where('cart.club = :club_id')
    ->andWhere('subscriptions.clubSeason = :season_id')
    ->setParameter('club_id', $club)
    ->setParameter('season_id', $season);
    if($section != null && !is_array($section)){
        $query->andWhere('subscriptions.section = :section_id')
        ->setParameter('section_id', $section);
    } elseif($section != null && is_array($section)){
        $query->andWhere('subscriptions.section IN :section_ids')
        ->setParameter('section_ids', $section);
    }
    $query->orderBy('cart.transaction_date','DESC');

    return $query->getQuery()->getResult();

I've got this error in return :

[Syntax Error] line 0, col 28: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got 'member'

In fact I try to hydrate my result collection with members in it. Member is manyToOne property in my Subscription entity. It is defined like this :

/**
 * @ORM\ManyToOne(targetEntity="Comiti\UserBundle\Entity\UserComiti", inversedBy="subscriptions", cascade={"persist"})
 * @ORM\JoinColumn(name="member_id", referencedColumnName="id")
 */
protected $member;

Look at error. Error said you had got syntax error in subquery — Rider_BY
What you posted seems right. Comment out everything after the second leftJoin just to verify it is the join. And of course do the clear cache thing if you just modified your associations. — Cerad
The query you posted is in a Controller, or in a Repository ? If it's a repository, it's related to which entity ? — progg

Timo Hermans Timo Hermans · Accepted Answer · 2019-09-01T07:55:07

So I ran into the exact same error when trying to join a category group for a category entity. See the code below:

    $qb = $this->entityManager->createQueryBuilder()
            ->select('c')
            ->from(Category::class, 'c');

        if (isset($fields['categoryGroup'])) {
            $qb = $qb->join('c.categoryGroup', 'group')
                ->addSelect('group');
        }

        $qb = $qb->getQuery()
            ->getArrayResult();

This resulted in the exact same error as the OP, only with 'group'. My guess is that you cannot use database reserved words in the query, like group and member.

For me, changing the alias to something like cg worked just fine.

Doctrine query error on leftJoin

1 Answers