注释查询集是否存在匹配的相关对象 [英] Annotate queryset with whether matching related object exists

问题描述

我有两个具有明确的多对多关系的模型：一个事物，auth.user和一个连接这两者的收藏夹模型。我希望能够通过东西是否被特定用户喜欢来订购它们。在Sqlite3中，我提出的最佳查询（大致）是：

I have two models with an explicit many-to-many relationship: a thing, auth.user, and a "favorite" model connecting the two. I want to be able to order my "thing"s by whether or not they are favorited by a particular user. In Sqlite3, the best query i've come up with is (roughly) this:

select 
    *, max(u.name = "john cleese") as favorited 
    from thing as t 
        join favorite as f on f.thing_id = t.id 
        join user as u on f.user_id = u.id
    group by t.id
    order by favorited desc
    ;

在我的sql-django翻译中，绊倒我的是 max（u.name = john cleese）位。据我所知，Django支持算术运算，但不支持相等运算。我能找到的最接近的是一个case语句，它不能正确地将输出行分组：

The thing tripping me up in my sql-to-django translation is the max(u.name = "john cleese") bit. As far as I can tell, Django has support for arithmatic but not equality. The closest I can come is a case statement that doesn't properly group the output rows:

Thing.objects.annotate(favorited=Case(
    When(favorites__user=john_cleese, then=Value(True)),
    default=Value(False),
    output_field=BooleanField()
))

我尝试过的另一个方向是使用 RawSQL ：

The other direction I've tried is to use RawSQL:

Thing.objects.annotate(favorited=RawSQL('"auth_user"."username" = "%s"', ["john cleese"]))

但是，这不起作用，因为（我知道）无法显式加入我需要的收藏夹和 auth_user 表。

However, this won't work, because (as far as I'm aware) there's no way to explicitly join the favorite and auth_user tables I need.

有什么我想念的吗？

推荐答案

这将实现您的目标（或

Thing.objects.annotate(
    favorited=Count(Case(
        When(
            favorites__user=john_cleese, 
            then=1
        ),
        default=0,
        output_field=BooleanField(),
    )),
)

这篇关于注释查询集是否存在匹配的相关对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！