Django: why does annotate add items to my queryset?

Question

I'm struggling to do something simple. I have Item objects, and users can mark them favorites. Since these items do not belong to user, I decided to use a ManyToMany between User and Item to record the favorite relationship. If the user is in favoriters item field, it means the user favorited it.

Then, when I retrieve objects for a specific user, I want to annotate each item to specify if it's favorited by the user. I made the add_is_favorite_for() method for this.

Here is the (simplified) code:

class ItemQuerySet(query.QuerySet):
    def add_is_favorite_for(self, user):
        """add a boolean to know if the item is favorited by the given user"""
        condition = Q(favoriters=user)
        return self.annotate(is_favorite=ExpressionWrapper(condition, output_field=BooleanField()))

class Item(models.Model):
    objects = Manager.from_queryset(ItemQuerySet)()

    favoriters = models.ManyToManyField(settings.AUTH_USER_MODEL, blank=True)

This does not work as expected, it seems Django adds an item for every user that favorited the item. It leads to crazy things like:

Item.objects.count() # 10
Item.objects.add_is_favorite_for(some_user).count() # 16 -> how the hell an annotation can return more results than initial queryset?

I'm missing something here...

The filter used in your annotation favoriters=user translates to a join at the SQL level which is why each Item will be repeated for every user that favourited it. It's possible that just adding distinct() to the query might fix the issue, otherwise the annotation can be changed to prevent this from happening — Iain Shelvington
– Iain Shelvington, Commented Dec 21, 2021 at 12:55
@IainShelvington distinct() does not work as expected, but can you elaborate about how changing the annotation itself to prevent this from happening? Thanks. — David Dahan
– David Dahan, Commented Dec 21, 2021 at 13:29

David Dahan · Accepted Answer · 2021-12-21 14:07:48Z

1

You can change the condition in the annotation to use a subquery, get all Items that the user has favourited and then test to see if the Item's id is in the subquery.

This should remove your issue with duplication:

def add_is_favorite_for(self, user):
    return self.annotate(is_favorite=ExpressionWrapper(
        Q(id__in=Item.objects.filter(favoriters=user).values('id')),
        output_field=BooleanField()
    ))

edited Dec 21, 2021 at 14:07

David Dahan

11.2k14 gold badges76 silver badges157 bronze badges

answered Dec 21, 2021 at 13:51

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Dahan Over a year ago

Thanks a lot, it works! I'm curious is this is the recommended way to do it or more like a workaround? I can't find official doc about this use case.

Collectives™ on Stack Overflow

Django: why does annotate add items to my queryset?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related