Django使用过滤器离开了外连接 [英] Django left outer join with filter

问题描述

我正在使用Django的内置用户模型，并拥有一个带有ForeignKey给用户的自定义Foo对象。我正在寻找选择所有用户对象和所有适合某些约束的Foo对象，如下所示：

  SELECT * from auth_user LEFT OUTER JOINfooON 
（auth_user.id = foo.id AND< other criteria here>）
  

如何在Django中完成这个？到目前为止我已经尝试过：

  User.objects.filter（foo __< criteria>）
  

，但生成与此类似的SQL：

  SELECT * from auth_user LEFT OUTER JOINfooON 
（auth_user.id = foo.id）WHERE<其他条件此处> 
  

，只返回具有符合条件的Foo对象的用户对象。或者，我可以选择所有用户对象，并为每个对象运行一个查询，但这将显着降低效率。

解决方案

如果想要Django获取与用户对象相关的所有用户对象和所有 Foo 对象， d使用 select_related（） ：

  User.objects.all（）。select_related （'foo'）
  

但这里你不希望所有 Foo 与用户对象相关的对象，您只需要满足您的条件的子集。我不知道一种方法来告诉Django在单个查询集中执行此操作。但是你可以做的是分别进行选择并在Python中进行加入：

 ＃从用户ID映射到相应的Foo满足< criteria>，如果有的话。 
 foos = {foo.user_id：foo for foo in 
 Foo.objects.filter（user__isnull = False，< criteria>）} 
为User.objects.all（）中的用户： 
 foo = foos.get（user.id）
＃... 
  

（这不会比您的 LEFT OUTER JOIN 更多的数据库工作或传输任何数据，所以我认为这是一个合理的方法。）

I'm using Django's built-in user model and have a custom Foo object with a ForeignKey to User. I'm looking to select all User objects and all of the Foo objects that fit certain constraints, like so:

SELECT * from auth_user LEFT OUTER JOIN "foo" ON
(auth_user.id = foo.id AND <other criteria here>)

How should I accomplish this in Django? So far I've tried:

User.objects.filter(foo__<criteria>)

but that generates SQL similar to this:

SELECT * from auth_user LEFT OUTER JOIN "foo" ON
(auth_user.id = foo.id) WHERE <other criteria here>

and only returns User objects that have Foo objects that fit the criteria. Alternately I can select all User objects and run a query for each one, but that would be substantially less efficient.

解决方案

If you wanted Django to fetch all the User objects and all the Foo objects that are related to a user object, then you'd use select_related():

User.objects.all().select_related('foo')

but here you don't want all the Foo objects that are related to a user object, you just want the subset of them that satisfy your criteria. I don't know of a way to tell Django to do that in a single query set. But what you can do is to do both selects separately and do the join in Python:

# Map from user id to corresponding Foo satisfying <criteria>, if any.
foos = {foo.user_id: foo for foo in 
        Foo.objects.filter(user__isnull = False, <criteria>)}
for user in User.objects.all():
    foo = foos.get(user.id)
    # ...

(This doesn't do any more database work or transfer any more data than your LEFT OUTER JOIN would, so I think it's a reasonable approach.)

这篇关于Django使用过滤器离开了外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！