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.
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.