Django queryset not returning the same values as the generated sql
- by HRCerqueira
Hello guys, I have the following queryset:
subscribers = User.objects.values('email', 'username').filter(
Q(subscription_settings__new_question='i') |
Q(subscription_settings__new_question_watched_tags='i',
marked_tags__id__in=question.tags.values('id'),
tag_selections__reason='good')
).exclude(id=question.author.id)
The problem is that when I evaluate the query I get only the values
that are filtered by the first Q object (even if I reverse the order
of the objects). So lets say that I was expecting the user A, B, C and
D, where A and B are filtered by the first Q object and C and D by the
second. But the queryset only returns A and B.
I used the django debug toolbar to see the query that was actually
being executed (and then I used a direct print statement like "print
subscriber.query.as_sql()" just to be sure) and then evaluated the
query directly using psql (I'm using postgres by the way), and I get
the results I expect.
Here's the query btw:
SELECT "auth_user"."email", "auth_user"."username" FROM "auth_user"
LEFT OUTER JOIN "forum_markedtag" ON ("auth_user"."id" =
"forum_markedtag"."user_id") INNER JOIN
"forum_defaultsubscriptionsetting" ON ("auth_user"."id" =
"forum_defaultsubscriptionsetting"."user_id") WHERE
((("forum_markedtag"."reason" = E'good' AND
"forum_defaultsubscriptionsetting"."new_question_watched_tags" = E'i'
AND "forum_markedtag"."tag_id" IN (SELECT U0."id" FROM "tag" U0 INNER
JOIN "question_tags" U1 ON (U0."id" = U1."tag_id") WHERE
U1."question_id" = 64 )) OR
"forum_defaultsubscriptionsetting"."new_question" = E'i' ) AND NOT
("auth_user"."id" = 10 ))
Thanks in advance
EDIT:
I tried to break the queryset into two, one that uses the first Q object as the filter and another one with the second Q object, and although the second queryset produces the correct sql that returns the correct values when evaluated directly, it still doesn't return nothing when evaluated as a django queryset.
heres the alternative code:
subscribers = User.objects.values('email', 'username').filter(
subscription_settings__new_question='i').exclude(id=question.author.id)
subscribers2 = User.objects.values('email', 'username').filter(
subscription_settings__new_question_watched_tags='i',
marked_tags__id__in=question.tags.values('id'),
tag_selections__reason='good').exclude(id=question.author.id)
