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.

Answer 1

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