id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25112	a second annotation filters results	karyon	nobody	"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)."	Bug	closed	Database layer (models, ORM)	1.8	Normal	duplicate		karyon	Unreviewed	0	0	0	0	0	0