a second annotation filters results

[karyon]

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).

[karyon]

is fixed in 1.8.3, i guess this is a dup of #24924.