Boolean annotation resulting in duplicates?

Question

I am trying to implement a basic 'favourites' system based on a foreign key table.

Let's say I have the following simple models:

class Item(models.Model)
  id = models.IntegerField()

class User(models.Model)
  id = models.IntegerField()

class UserFavourites(models.Model)
  user = models.ForeignKey(User, related_name='user_for_fav_rel')
  item = models.ForeignKey(Item, related_name='item_for_fav_rel')

Now I generate the following queryset for items to see if they have been marked as a favourite by the user:

queryset = Item.objects.all()

USER_ID = request.user.id

queryset = queryset.annotate(
    favourite=Case(
        When(
            item_for_fav_rel__user__id=USER_ID,
            then=Value('True')
        ),
        default=Value('False'),
        output_field=BooleanField()
    )
)

All of this works great, but in the response, if the item has indeed been favourited, I receive a duplicate of that particular item in the queryset. Any idea how to avoid this?

Resultant SQL Query (edited down to the minimal example I think...)

SELECT 
    "core_item"."id", 
    CASE 
        WHEN "core_userfavourites"."user_id" = 1 THEN True 
        ELSE False 
    END AS "favourite" 
FROM "core_item" 
LEFT OUTER JOIN "core_userfavourites" 
    ON ("core_item"."id" = "core_userfavourites"."item_id")

Is there a particular reason why you use `all()` and then `annotate() `? — scharette, Nov 22 '17 at 20:54
@sharette This is a small piece of a much larger program that uses django rest framework. I was trying to skim it down in a hurry. — Eric, Nov 22 '17 at 20:55

score 2 · Accepted Answer · answered Nov 22 '17 at 21:20

2

The problem is you're getting one row for each combination of core_item and core_userfavorites. There doesn't seem to be a way to do exactly what you're trying without raw SQL, but fortunately Django very recently (1.11) added the ability to write SubQuery clauses which you can use here

from django.db.models.expressions import OuterRef, Subquery

queryset = Item.objects.all()

USER_ID = request.user.id
user_favorites = UserFavourites.objects.filter(
    user_id=USER_ID, 
    item_id=OuterRef('id')
)[:1].values('user_id')

queryset = queryset.annotate(user_favorite=Subquery(user_favorites))

This will give you the the user_id in the user_favorite field if the user has favorited it, None if they have not.

Basically you're writing a subquery to pick an arbitrary value from the related table.

answered Nov 22 '17 at 21:20

Vinay Pai

7,432
1
17
28

Thanks Vinay! I'll try this out later tonight. Questions: Why are you slicing the subquery? Is this adaptable to a Boolean result? – Eric Nov 22 '17 at 23:23
A query will fail if the subquery returns more than one row. Might not be necessary in this case. You probably could turn this into a Boolean result by wrapping the subquery in a case like you had in your original expression. I'm not sure it's worth it though considering None will evaluate to False and other values of user_id will evaluate to True. I'll admit it's a bit ugly though. It would be nice if exists() could return a queryset or another chainable object instead of triggering execution and returning a value immediately. – Vinay Pai Nov 22 '17 at 23:29
1

Thanks again! This works great. I did manage to wrap it into True/False in the serializer for 'item' as part of Django Rest Framework. – Eric Nov 23 '17 at 15:08

Boolean annotation resulting in duplicates?

1 Answers1

Linked