Stuck on a DQL left join query

Question

I have two tables:

Users <----> Questions

They use a ManyToMany relation table to keep track of which user answered which question.

Users <----> UsersQuestions <----> Questions

Now I need to write a query which fetches all the questions a specific user id has NOT answered yet.

The following native query works fine:

SELECT * FROM questions q
WHERE q.id NOT IN (
    SELECT question_id FROM users_questions 
    WHERE user_id = 4
);

But I directly access the UsersQuestions table in this query and I haven't found a way to do so in Doctrine yet, I don't even think it's possible.

The only way to access that table is by joining on a property of my Question class, thus I tried to rewrite the query to the following one which also works fine as a native query:

SELECT * questions q
LEFT JOIN users_questions uq
    ON q.id = uq.question_id AND uq.user_id = 4
WHERE uq.user_id IS NULL;

I was assuming that I could simply rewrite this into DQL as the following query:

SELECT q FROM MyBundle:Question q
LEFT JOIN q.usersAnswered uq WITH uq.id = 4 
WHERE uq.id IS NULL

When I call $query->getSql() I get the following output:

SELECT * FROM mybundle_questions g0_ 
LEFT JOIN users_questions u2_ 
    ON g0_.id = u2_.question_id 
LEFT JOIN mybundle_users g1_ 
    ON g1_.id = u2_.user_id AND (g1_.id = 4) 
WHERE g1_.id IS NULL

Which to me looks fine given my very basic knowledge on Doctrine and queries in general. However, this fetches and returns ALL the questions which are in the table and not only the ones which this users hasn't answered yet.

Did I make a mistake somewhere? Or is there any other/easier way to fetch these unanswered questions? I feel like I'm reinventing the wheel here.

Been stuck on this for days and every attempt that I make in native SQL works fine, but I can't translate it to DQL. Any help would be appreciated.

From the Doctrine website: A common mistake for beginners is to mistake DQL for being just some form of SQL and therefore trying to use table names and column names or join arbitrary tables together in a query. You need to think about DQL as a query language for your object model, not for your relational schema. — Jim Mc, Jun 03 '15 at 18:47
I'm aware of that. As far as I know I'm not making this mistake here. In my third query I correctly implement the join from an object model standpoint, not the traditional table one. In a traditional query the uq.id refers to the row id of the UsersQuestions so this query wouldn't work there at all. However it does work because in how DQLs' logic works, so I think I got the concepts right. But I'm missing something else. — Rijstkoek, Jun 04 '15 at 10:08
It would also be nice if people told me why my questions is being downvoted. — Rijstkoek, Jun 06 '15 at 10:58

score 0 · Accepted Answer · answered Jun 07 '15 at 15:21

I've solved my problem with the following query. Looks like it WAS possible to create a subquery after all.

SELECT q FROM MyBundle:Question q
WHERE q.id NOT IN (
    SELECT uq.id FROM MyBundle:User u 
    INNER JOIN u.questionsAnswered uq 
    WHERE u.id = 4
)

Which Doctrine translates to the following query:

SELECT * FROM myBundle_questions g0_
WHERE g0_.id NOT IN (
    SELECT g1_.id FROM myBundle_users g2_ 
    INNER JOIN users_questions u3_ 
        ON g2_.id = u3_.user_id 
    INNER JOIN myBundle_questions g1_ 
        ON g1_.id = u3_.question_id 
    WHERE g2_.id = ?
)

For some reason the previous query, although it looked good in my eyes, didn't work. Must have something to do with how Doctrine handles the left join / null situations. However, this query in which I approach the problem from a different angle work perfectly.

Stuck on a DQL left join query

1 Answers1