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?

Answer 1

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