Doctrine's left join behaves different than plain sql

Question

I have this symfony app with a table of questions and a table of answers (results) associated to each question:

Questions table:

id | question_title
-------------------
1  | Name?
2  | Age?

Results table:

id | answer_text | question_id | user_id
----------------------------------------
1  | John        | 1           | 10
2  | Peter       | 1           | 11
3  | 24          | 2           | 10

A user may skip a question, thus it might not be a matching answer for a given question in the answers table. But when i retrieve the results for a given user, i want a comprehensive list of every question and the associated answer or null, in case it doesn't exist. So (in plain SQL) i left join like this:

 SELECT question_text, answer_text FROM `question` left join result on question.id = result.question_id and user_id=10

Gets me:

question_text | answer_text
----------------------------------------
Name?         | John
Age?          | 24

For the user_id 11, this looks like:

question_text | answer_text
----------------------------------------
Name?         | Peter
Age?          | null

Just what I expect.

The problem arises when i try to translate this query into a dql query. Im doing it like this:

    $qb->select('q.question_title, r.answer_text');
    $qb->from('AppBundle:Question', 'q');
    $qb->leftJoin(
        'AppBundle:Result',
        'r',
        \Doctrine\ORM\Query\Expr\Join::WITH,
        'q.id = r.question'
    );
    $qb->where('r.user = '.$user->getId());

    $answer= $qb->getQuery()->getResult();

For datasets that have matching data on the right side of the join it works fine. But when the right side is null, it gets stripped out of the array returned but getResult. This would be the dump for the first example above in plain SQL:

array:2 [
    0 => array:2 [
        "question_title" => Name?
        "answer_text" => "John"
    ]
    1 => array:2 [
        "question_title" => Age?
        "answer_text" => "24"
    ]
]

And this is the dump for the second example. There is no matching answer and i get just an array of 1 element:

array:2 [
    0 => array:2 [
        "question_title" => Name?
        "answer_text" => "Peter"
    ]
]

I don't know if there is any way to mimic the exact behaviour i get with a left join in plain sql.

By the way the database engine im using is mysql, just in case that makes any difference.

EDIT: not actually what is being asked in Doctrine2 LEFT JOIN with 2 conditions. Thought I had the same query behaving different in DQL and SQL. Turned out that I just screwed it up on the translation.

Possible duplicate of [Doctrine2 LEFT JOIN with 2 conditions](https://stackoverflow.com/questions/15815869/doctrine2-left-join-with-2-conditions) This is obviously basic, what did you find googling, say, 'doctrine left join multiple on'? — philipxy, Oct 13 '17 at 21:16
That was not really what I was asking. I thought i had a query in SQL behaving differently in DQL, but I was actually translating it wrong, as the user below noted. — Kilian Perdomo Curbelo, Oct 16 '17 at 17:04
What is *asked* in that question (despite its (poor) title) is why results are wrong in a certain way given some `join`s & a `where`. So a reasonable characterization of the question includes your situation. What is *answered* in that question is that the asker is erroneously putting a condition in a `where` when they need it in an `on`. That is the answer to your question. Which is also in many of other hits, also hits from variations. I *found* that answer by the google I gave. So... — philipxy, Oct 16 '17 at 21:40
Please : 1 Always think: Is this likely a *new* question? Because it probably isn't. 2 Always google many clear, concise & specific (but without application-specific names) phrasings of your question/problem/desiderata & combinations of tags & read *many* hits. 3 If you find no answer then make 1 google into a title. 4 Always give a [mcve]. Without which a question should be closed (blocked from answers) and elicits comments for clarifications & "answer" posts that are just guesses and should not have been posted. 5 All this is in [ask] & other [help] links so please keep reading those. — philipxy, Oct 16 '17 at 21:57

score 1 · Accepted Answer · answered Oct 13 '17 at 20:21

If you see DQL query you are trying apply WHERE clause on the joined result set, so your filter works correctly and filter out the results where user id is not 11.

Now if you look at your SQL query you are not using WHERE clause your are trying to join results table with question id and user is 11 so it will join the rows from results table where user id is 11 and produces null for non matching rows.

There is a difference having a filter on ON clause and WHERE, Filter on ON clause will work only at the joining time while WHERE is applied to the joined result set (after performing join operation).

To write a DQL equivalent to your SQL it can be written as

SELECT q.question_title, r.answer_text
FROM AppBundle:Question q
LEFT JOIN AppBundle:Result r 
     WITH q.id = r.question AND r.user = :user

Its better to have a mapping defined in your entities rather than performing a join manually Association Mapping

Doctrine's left join behaves different than plain sql

1 Answers1