django orm-如何在其父类的子类的外键上使用select_related() [英] django orm - How to use select_related() on the Foreign Key of a Subclass from its Super Class

问题描述

我总是发现Django orm对子类化模型的处理非常繁琐.这可能就是为什么我遇到这样的问题.

I've always found the Django orm's handling of subclassing models to be pretty spiffy. That's probably why I run into problems like this one.

采取三种模式:

class A(models.Model):
    field1 = models.CharField(max_length=255)

class B(A):
    fk_field = models.ForeignKey('C')

class C(models.Model):
    field2 = models.CharField(max_length=255)

因此，现在您可以查询A模型并获取所有B模型(如果有):

So now you can query the A model and get all the B models, where available:

the_as = A.objects.all()
for a in the_as:
    print a.b.fk_field.field2 #Note that this throws an error if there is no B record

问题是您正在查看大量的数据库调用以检索所有数据.

The problem with this is that you are looking at a huge number of database calls to retrieve all of the data.

现在假设您想检索数据库中所有所有A模型的QuerySet，但同时包含所有子类记录和子类的外键记录，请使用select_related()将应用程序限制为单个数据库调用.您将这样编写查询:

Now suppose you wanted to retrieve a QuerySet of all A models in the database, but with all of the subclass records and the subclass's foreign key records as well, using select_related() to limit your app to a single database call. You would write a query like this:

the_as = A.objects.select_related("b", "b__fk_field").all()

一个查询返回所有需要的数据！很棒.

One query returns all of the data needed! Awesome.

除外.因为此版本的查询正在执行其自己的过滤，所以即使select_related都不应该完全过滤任何结果:

Except not. Because this version of the query is doing its own filtering, even though select_related is not supposed to filter any results at all:

set_1 = A.objects.select_related("b", "b__fk_field").all() #Only returns A objects with associated B objects
set_2 = A.objects.all() #Returns all A objects
len(set_1) > len(set_2) #Will always be False

我使用django-debug-toolbar检查查询并发现了问题.生成的SQL查询使用将C表联接到查询，而不是像其他子类字段一样使用LEFT OUTER JOIN:

I used the django-debug-toolbar to inspect the query and found the problem. The generated SQL query uses an INNER JOIN to join the C table to the query, instead of a LEFT OUTER JOIN like other subclassed fields:

SELECT "app_a"."field1", "app_b"."fk_field_id", "app_c"."field2"
FROM "app_a" 
    LEFT OUTER JOIN "app_b" ON ("app_a"."id" = "app_b"."a_ptr_id") 
    INNER JOIN "app_c" ON ("app_b"."fk_field_id" = "app_c"."id");

看来，如果我只是将INNER JOIN更改为LEFT OUTER JOIN，那么我会得到所需的记录，但这在使用Django的ORM时无济于事.

And it seems if I simply change the INNER JOIN to LEFT OUTER JOIN, then I get the records that I want, but that doesn't help me when using Django's ORM.

这是Django ORM中select_related()中的错误吗?有没有解决的办法，还是我只需要直接查询数据库并自己映射结果?我应该使用类似Django-Polymorphic的方法来执行此操作吗?

Is this a bug in select_related() in Django's ORM? Is there any work around for this, or am I simply going to have to do a direct query of the database and map the results myself? Should I be using something like Django-Polymorphic to do this?

推荐答案

它看起来像个错误，特别是它似乎忽略了A-> B关系的可空性质，例如，如果您有一个外键引用， A中的B而不是子类，该外键当然可以为空，而django将为其使用左连接.您可能应该在django问题跟踪器中提出此问题.您也可以尝试使用prefetch_related而不是select_related来解决问题.

It looks like a bug, specifically it seems to be ignoring the nullable nature of the A->B relationship, if for example you had a foreign key reference to B in A instead of the subclassing, that foreign key would of course be nullable and django would use a left join for it. You should probably raise this in the django issue tracker. You could also try using prefetch_related instead of select_related that might get around your issue.

这篇关于django orm-如何在其父类的子类的外键上使用select_related()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！