用左外连接注释Django查询集？ [英] Annotating a Django queryset with a left outer join?

问题描述

说我有一个模型：

  class Foo（models.Model）：
 ... 
  

另一个基本上给出每个用户关于 Foo ：

  class UserFoo（models.Model）：
 user = models.ForeignKey（User）
 foo = models.ForeignKey（Foo）
 ... 
 
 class Meta：
 unique_together =（user，foo）
  

我想生成一个 Foo 的查询，但用可选）相关的 UserFoo 基于 user = request.user 。

所以它实际上是一个 LEFT OUTER JOIN on（foo.id = userfoo.foo_id AND userfoo.user_id = ...）

解决方案

raw 的解决方案可能看起来像

  foos = Foo.objects.raw（SELECT foo。* FROM foo LEFT OUTER JOIN userfoo ON（foo.id = userfoo.foo_id AND foo.user_id =％s），[req uest.user.id]）
  

您需要修改 SELECT 要包含来自 userfoo 的额外字段，该字段将被注释到生成的 Foo 实例中queryset。

Say I have a model:

class Foo(models.Model):
    ...

and another model that basically gives per-user information about Foo:

class UserFoo(models.Model):
    user = models.ForeignKey(User)
    foo = models.ForeignKey(Foo)
    ...

    class Meta:
        unique_together = ("user", "foo")

I'd like to generate a queryset of Foos but annotated with the (optional) related UserFoo based on user=request.user.

So it's effectively a LEFT OUTER JOIN on (foo.id = userfoo.foo_id AND userfoo.user_id = ...)

解决方案

A solution with raw might look like

foos = Foo.objects.raw("SELECT foo.* FROM foo LEFT OUTER JOIN userfoo ON (foo.id = userfoo.foo_id AND foo.user_id = %s)", [request.user.id])

You'll need to modify the SELECT to include extra fields from userfoo which will be annotated to the resulting Foo instances in the queryset.

这篇关于用左外连接注释Django查询集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！