Opened 9 years ago
Closed 9 years ago
#24766 closed Bug (fixed)
annotation with Q object filters results
Reported by: | karyon | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.8 |
Severity: | Normal | Keywords: | |
Cc: | josh.smeaton@…, python@… | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I tried to annotate a query of UserProfiles with a boolean indicating whether each user is in a specific group or not. Besides the fact that i didn't find any obvious or easy to find solution for that, i encountered the following oddity:
Any of these:
UserProfile.objects.annotate(is_member=Case(When(groups__name="Group name", then=Value(True)), default=Value(False), output_field=BooleanField()))
UserProfile.objects.annotate(is_member=ExpressionWrapper(Q(groups__name="Group name"), output_field=BooleanField()))
returns only users that are in some group. Users without groups are not in the result. This was unexpected since i didn't request any filtering.
The issue might be that both queries use inner joins. here's the sql:
'SELECT <snip>, CASE WHEN "auth_group"."name" = Group name THEN True ELSE False END AS "is_member" FROM "evaluation_userprofile" INNER JOIN "evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" = "evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN "auth_group" ON ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'
'SELECT <snip>, "auth_group"."name" = Group name AS "is_member" FROM "evaluation_userprofile" INNER JOIN "evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" = "evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN "auth_group" ON ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'
Change History (8)
comment:1 by , 9 years ago
Description: | modified (diff) |
---|
comment:2 by , 9 years ago
Cc: | added |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:3 by , 9 years ago
comment:4 by , 9 years ago
Cc: | added |
---|
comment:7 by , 9 years ago
Resolution: | fixed |
---|---|
Status: | closed → new |
This bug still occurs if you've got more than one annotation on the same table.
Say you've got 3 Pizza objects having an m2m with Toppings. One pizza has pepperoni, one mushrooms, and one is just plain cheese with no toppings.
Single annotation:
$> annotations = { 'pepperoni_ct': Count(Case(When(toppings__name='pepperoni', then='toppings'))) } $> Pizza.objects.annotate(**annotations).count() 3
We correctly promote the join to OUTER and get all three Pizza objects.
But now with two annotations:
$> annotations = { 'pepperoni_ct': Count(Case(When(toppings__name='pepperoni', then='toppings'))) 'mushroom_ct': Count(Case(When(toppings__name='mushrooms', then='toppings'))) } $> Pizza.objects.annotate(**annotations).count() 2
The join is not promoted and we only get two Pizza objects.
comment:8 by , 9 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
As the fix for this ticket has already been released, please open a new ticket for further problems. In this case, I wonder if the issue you describe might be a symptom of #10060 ("Multiple table annotation failure").
It seems #24753 is closely related; the difference is that the annotations there are aggregates.