annotation with Q object duplicates results

[karyon]

consider the following models:

class UserProfile(models.Model):
    pass

class Group(models.Model):
    name = models.CharField(max_length=255)
    users = models.ManyToManyField(UserProfile, related_name='groups')

and the following test:

def test_annotation(self):
    group1 = Group.objects.create(name="group1")
    group2 = Group.objects.create(name="group2")
    user = UserProfile.objects.create()
    user.groups = [group1, group2]
    users = UserProfile.objects.all().annotate(is_group1=ExpressionWrapper(Q(groups__name="group1"), output_field=BooleanField()))
    for u in users:
        print(u.is_group1)
    print(users.query)
    self.assertEqual(users.count(), 1)

this prints the following:

Creating test database for alias 'default'...
1
0
SELECT "testapp_userprofile"."id", "testapp_group"."name" = group1 AS "is_group1" FROM "testapp_userprofile" LEFT OUTER JOIN "testapp_group_users" ON ( "testapp_userprofile"."id" = "testapp_group_users"."userprofile_id") LEFT OUTER JOIN "testapp_group" ON ( "testapp_group_users"."group_id" = "testapp_group"."id" )
F
<snip>
AssertionError: 2 != 1

the annotation is supposed to add a bool to the user indicating whether that user is part of some group or not. i haven't found an easier way to do it. this worked for us until we had a user that was in two groups at the same time.

for some reason the annotation with the Q object causes the user the appear two times in the result, once with is_group1==True, once with is_group1==False. if i make the user have only one group, the test succeeds.

[Tim Graham]

Duplicate of #10060

[Anssi Kääriäinen]

This is a bit different from #10060, as there is no aggregation going on here. The problem is that a single user has multiple groups, and you'll get a different annotation for each of the groups.

You'll likely get the correct result if you add filter(Q(groups__name='group1')|Q(groups__isnull=True)) *before* the annotate() call. This will work as the user can have at most one group named group1.

I think there would be room for adding a generic query operator for asking "annotate True to users which have at least (or at most) N groups matching condition X". Your case is "at least 1 group named 'group1'", with the extra constraint that group's name is unique. In the more general cases answering this question requires aggregation. Notably you could also solve your problem by conditional aggregation where you count all groups that are named 'group1', and then annotate True if result is greater or equal to 1.

[karyon]

@akaariai, in #25112 i made an attempt at the conditional aggregate you mentioned, although i wasn't quite sure how to do it. the first suggestion you made ( filter(Q(groups__name='group1')|Q(groups__isnull=True)) for some reason filtered all users that had no group, and even if it didn't, it looks like it should filter all users that are in a group other than group1, right?