Using Django ORM, how to perform this INNER JOIN type of query ?

Question

Hi, I have been looking for an answer for this problem.

I have a schema with these models

• Users.
• Questions.
• Answers given by users to a question.

You understand there are 2 foreign keys in an answer:

• One foreign key to Question
• Another one to User

I want to get a list of rows containing the commons questions answered by 2 users, that is: Return rows containing both the answers of userA and userB to questions they have both answered. (It seems that it was a good method to make some calculation)

In SQL the query looks like:

$sql = "SELECT <fields...> 
        FROM {votes} AS v1 INNER JOIN {votes} as v2 
        ON (v1.user = %d AND v2.user = %d AND v1.question = v2.question)";

So if userA answered questions 1, 2, 3, 4, 5 and userB answered questions 2, 4 and 7, the query would return these rows : * v1.user=userA, v1.question=2, v2.user=userB, v2.question=2, ... * v1.user=userA, v1.question=4, v2.user=userB, v2.question=4, ...

I've tried many things with the ORM, the extra() function with the 'tables' argument is ignored by django, nothing works. How one could execute a query like this, with no raw SQL (with extra() at most) ?

Thanks

Answer 1

Since I'm fairly new to Django myself & have only had to do simple queries so far, perhaps I'm missing something. But wouldn't

a_ans = Answer.objects.filter(user=a, question__answer__user=b)
b_ans = Answer.objects.filter(user=b, question__answer__user=a)
overall = a_ans|b_ans

do what you're looking for? Or if not that, breaking the restrictions for each into two separate filters?