#25112: a second annotation filters results
-------------------------------+--------------------
Reporter: karyon | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
over several months now i'm trying to annotate a list of users with
booleans indicating whether each user is in a certain group or not. about
a dozen of attempts all didn't work (partly because of bugs, e.g. #25028).
this is the latest attempt, by now the code reflects my desperateness :)
{{{
users = UserProfile.objects.all() \
.annotate(group1_count=Sum(
Case(When(groups__name="Group1", then=1),
output_field=IntegerField(
.annotate(is_group1=
ExpressionWrapper(Q(group1_count__exact=1),
output_field=BooleanField())) \
}}}
this seems to work like expected. however, when adding a second set of
annotations in a similar fashion for a second group, the results does not
contain all users that are in no group at all.
{{{
users = UserProfile.objects.all() \
.annotate(group1_count=Sum(
Case(When(groups__name="Group1", then=1),
output_field=IntegerField()))) \
.annotate(is_group1=
ExpressionWrapper(Q(group1_count__exact=1),
output_field=BooleanField())) \
.annotate(group2_count=Sum(
Case(When(groups__name="Group2", then=1),
output_field=IntegerField()))) \
.annotate(is_group2=
ExpressionWrapper(Q(group2_count__exact=1),
output_field=BooleanField())) \
}}}
again, all i'm trying to do is annotating users with booleans which tell
me whether each user is part of some group or not. i'd be glad to hear
suggestions on how to do that properly :)
the two queries:
{{{
SELECT "evaluation_userprofile"."id", "evaluation_userprofile"."password",
"evaluation_userprofile"."last_login",
"evaluation_userprofile"."is_superuser",
"evaluation_userprofile"."username", "evaluation_userprofile"."email",
"evaluation_userprofile"."title", "evaluation_userprofile"."first_name",
"evaluation_userprofile"."last_name",
"evaluation_userprofile"."login_key",
"evaluation_userprofile"."login_key_valid_until",
SUM(CASE WHEN "auth_group"."name" = 'Group1' THEN 1 ELSE NULL END) AS
"group1_count", SUM(CASE WHEN "auth_group"."name" = 'Group1' THEN 1 ELSE
NULL END) = 1 AS "is_group1"
FROM "evaluation_userprofile" LEFT OUTER JOIN
"evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" =
"evaluation_userprofile_groups"."userprofile_id" ) LEFT OUTER JOIN
"auth_group" ON ( "evaluation_userprofile_groups"."group_id" =
"auth_group"."id" )
GROUP BY "evaluation_userprofile"."id",
"evaluation_userprofile"."password",
"evaluation_userprofile"."last_login",
"evaluation_userprofile"."is_superuser",
"evaluation_userprofile"."username", "evaluation_userprofile"."email",
"evaluation_userprofile"."title", "evaluation_userprofile"."first_name",
"evaluation_userprofile"."last_name",
"evaluation_userprofile"."login_key",
"evaluation_userprofile"."login_key_valid_until"
ORDER BY "evaluation_userprofile"."last_name" ASC,
"evaluation_userprofile"."first_name" ASC,
"evaluation_userprofile"."username" ASC
}}}
{{{
SELECT "evaluation_userprofile"."id", "evaluation_userprofile"."password",
"evaluation_userprofile"."last_login",
"evaluation_userprofile"."is_superuser",
"evaluation_userprofile"."username", "evaluation_userprofile"."email",
"evaluation_userprofile"."title", "evaluation_userprofile"."first_name",
"evaluation_userprofile"."last_name",
"evaluation_userprofile"."login_key",
"evaluation_userprofile"."login_key_valid_until", SUM(CASE WHEN
"auth_group"."name" = 'Group1' THEN 1 ELSE NULL END) AS "group1_count",
SUM(CASE WHEN "auth_group"."name" = 'Group1' THEN 1 ELSE NULL END) = 1 AS
"is_group1",
SUM(CASE WHEN "auth_group"."name" = 'Group2' THEN 1 ELSE NULL END) AS
"group2_count", SUM(CASE WHEN "auth_group"."name" = 'Group2' THEN 1 ELSE
NULL END) = 1 AS "is_group2"
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" )
GROUP BY "evaluation_userprofile"."id",
"evaluation_userprofile"."password",
"evaluation_userprofile"."last_login",
"evaluation_userprofile"."is_superuser",
"evaluation_userprofile"."username", "evaluation_userprofile"."email",
"evaluation_userprofile"."title", "evaluation_userprofile"."first_name",
"evaluation_userprofile"."last_name",
"evaluation_userprofile"."login_key",
"evaluation_userprofile"."login_key_valid_until"
ORDER BY "evaluation_userprofile"."last_name" ASC,
"evaluation_userprofile"."first_name" ASC,
"evaluation_userprofile"."username" ASC
}}}
from what i can see the second query uses inner joins instead of left
outer joins, which might be the problem.
as a side note, i also don't quite understand why the SUM expression
appears two times in the first query (and four times in the second).
--
Ticket URL: <https://code.djangoproject.com/ticket/25112>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.e430237fbe3db723b05b503c00b20889%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
