DQL - leftJoin() error: Expected Literal, got 'JOIN'

Question

This code:

$builder->select('p')
    ->from('ProProposalBundle:Proposal', 'p')
    ->leftJoin('ProProposalBundle:Proposal:Vote', 'v')
    ->leftJoin('ProUserBundle:User', 'u')
    ->andWhere('v.proposal = p')
    ->andWhere('v.user = u')
    ->andWhere('v.decision = "in_favor" OR v.decision = "against"')
    ->andWhere('u = :user')
    ->setParameter('user', $options['user'])
    ->andWhere('p.community = :community')
    ->setParameter('community', $community)
    ->andWhere('p.archived = :archived')
    ->setParameter('archived', $options['archived'])
    ->leftJoin('p.convocation', 'c')
    ->andWhere("p.convocation IS NULL OR c.status = '" . Convocation::STATUS_PENDING . "'");

    return $builder->getQuery()->execute();

is returning an error:

[Syntax Error] line 0, col 106: Error: Expected Literal, got 'JOIN'

This is the formed query:

SELECT p FROM ProProposalBundle:Proposal p LEFT JOIN ProProposalBundle:Proposal:Vote v LEFT JOIN ProUserBundle:User u LEFT JOIN p.convocation c WHERE v.proposal = p AND v.user = u AND (v.decision = "in_favor" OR v.decision = "against") AND u = :user AND p.community = :community AND (p.convocation IS NULL OR c.status = 'pending') ORDER BY p.created desc

LEFT JOIN is missing the ON or WITH condition. The question is: what am I doing wrong with DQL query? Am I wrong with leftJoin() method?

AlterPHP · Accepted Answer · 2014-03-27T08:39:01.343

14

Doctrine ORM needs you to tell which relation is joined, not the entity itself (you did it well with p.convocation) :

$builder->select('p')
    ->from('ProProposalBundle:Proposal', 'p')
    ->leftJoin('ProProposalBundle:Proposal\Vote', 'v', 'WITH', 'v.proposal = p AND v.user = :user AND (v.decision = :in_favor OR v.decision = :against)')
    ->setParameter('user', $options['user'])
    ->setParameter('in_favor', 'in_favor')
    ->setParameter('against', 'against')
    ->andWhere('p.community = :community')
    ->setParameter('community', $community)
    ->andWhere('p.archived = :archived')
    ->setParameter('archived', $options['archived'])
    ->leftJoin('p.convocation', 'c')
    ->andWhere("p.convocation IS NULL OR c.status = :pending")
    ->setParameter('pending', Convocation::STATUS_PENDING);

return $builder->getQuery()->execute();

edit: I inversed Vote relation as you commented and removed useless WHERE clauses (Doctrine automatically resolves JOIN ON clause. I also transferred some WHERE clauses about joins in the optional params (WITH in DQL).

edit2: Without relation between Proposal and Vote, not sure it works.

edit3: Best practice is to use setParameter for all values in WHERE clauses.

edited Mar 27 '14 at 08:39

answered Mar 26 '14 at 11:03

AlterPHP

12,667
5
49
54

Oh! Well. The thing is that `p.vote` does not exist (only `v.proposal`). and neither `p.user` (only `v.user`). – Manolo Mar 26 '14 at 11:09
You should have inversed relations like p.votes and you can use v.user (I edited my answer) – AlterPHP Mar 26 '14 at 11:10
Any way to achieve it without adding relation like `p.votes`? It must be possible. – Manolo Mar 26 '14 at 11:20
Try my second edit, not sure it works like that but this link makes me confident : http://stackoverflow.com/questions/11116428/symfony-2-inner-join-on-non-related-table-with-doctrine-query-builder – AlterPHP Mar 26 '14 at 11:42
Now I'm getting this error: `[Syntax Error] line 0, col 158: Error: Expected Literal, got '"' `. New query is `SELECT p FROM ProProposalBundle:Proposal p LEFT JOIN ProProposalBundle:Proposal:Vote v WITH v.proposal = p AND v.user = :user AND (v.decision = "in_favor" OR v.decision = "against") LEFT JOIN p.convocation c WHERE p.community = :community AND (p.convocation IS NULL OR c.status = 'pending')` – Manolo Mar 26 '14 at 11:50
It was due to the double quotes. Now, I'm getting this error: `[Semantical Error] line 0, col 108 near 'proposal = p': Error: Class Pro\ProposalBundle\Entity\Proposal has no field or association named proposal`. But `v.proposal` exists in `ProProposalBundle:Proposal:Vote`, not in `ProProposalBundle:Proposal`. Any idea? – Manolo Mar 26 '14 at 12:36
Solved! It was the `Vote` class definition. Solved replacing `ProProposalBundle:Proposal:Vote` with `ProProposalBundle:Proposal\Vote`. – Manolo Mar 26 '14 at 13:17
Thank you! I think you are missing `:` before against (line 3) – Manolo Mar 26 '14 at 15:18

DQL - leftJoin() error: Expected Literal, got 'JOIN'

1 Answers1