布尔注释导致重复吗? [英] Boolean annotation resulting in duplicates?

问题描述

我正在尝试基于外键表实现基本的收藏夹"系统.

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

所有这些都很好用，但是在响应中，如果确实收藏了该商品，我将在queryset中收到该特定商品的副本.知道如何避免这种情况吗?

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?

结果SQL查询(编辑为我认为的最小示例...)

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

推荐答案

问题是，对于core_item和core_userfavorites的每种组合，您只能获得一行.似乎没有一种方法可以完全不用原始SQL来完成您要尝试的工作，但是幸运的是Django最近(1.11)添加了编写SubQuery子句的功能，您可以在此处使用

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

如果用户喜欢，则会在 user_favorite 字段中为您提供 user_id ，如果没有，则为 None .

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.

这篇关于布尔注释导致重复吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！