Can I use "ON" keyword in DQL or do I need to use Native Query?

Question

I have a oneToMany relationship between Post and PostVote. I would like to be able to retrieve a Post and how a specific user voted on it. In DQL I would like to retrieve Post and related PostVote entity, but only one where user_id is for example 5.

I don't seem to be able to use ON sql keyword like this:

->createQuery('SELECT p, pv FROM Post p LEFT JOIN p.postvotes ON pv.user = :userid WHERE p.id = :postid')

And if I use WHERE to filter out results, it create a problem and does not display a result unless at least one postvote object exists:

->createQuery('SELECT p, pv FROM Post p LEFT JOIN p.postvotes WHERE p.id = :postid' AND pv.user = :userid)

Is Native Query the only way I can achieve this?

score 20 · Accepted Answer · answered Jan 05 '12 at 19:43

20

You have to use the WITH keyword in DQL to accomplish this:

SELECT p, pv FROM Post p LEFT JOIN p.postvotes WITH pv.user = :userid WHERE p.id = :postid

answered Jan 05 '12 at 19:43

K. Norbert

10,494
5
49
48

1

After 6 years, this saved my life. Thanks. – Petrus Jun 11 '18 at 16:20
Same story bro (8 years after) :) Doctrine2 documentation link https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/dql-doctrine-query-language.html#from-join-and-index-by – FallDi Oct 23 '20 at 07:37
Absolutely a life saver – BogdanG May 12 '22 at 15:53

Can I use "ON" keyword in DQL or do I need to use Native Query?

1 Answers1

Linked