[FIXED] Django: why does annotate add items to my queryset?

Issue

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

Solution

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

Answered By - Iain Shelvington