注释查询集是否存在匹配的相关对象 [英] 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屋!