select_related with反向外键

I have two Models in Django. The first has the hierarchy of what job functions (positions) report to which other positions, and the second is people and what job function they hold.

我在Django有两种型号。第一个是职位职能(职位)报告的层级，其他职位是什么，第二是人，以及他们所拥有的工作职能。

class PositionHierarchy(model.Model):
    pcn = models.CharField(max_length=50)
    title = models.CharField(max_length=100)
    level = models.CharField(max_length=25)
    report_to = models.ForeignKey('PositionHierachy', null=True)


class Person(model.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    ...
    position = models.ForeignKey(PositionHierarchy)

When I have a Person record and I want to find the person's manager, I have to do

当我有个人记录，我想找到那个人的经理，我必须做

manager = person.position.report_to.person_set.all()[0]
# Can't use .first() because we haven't upgraded to 1.6 yet

If I'm getting people with a QuerySet, I can join (and avoid a second trip to the database) with position and report_to using Person.objects.select_related('position', 'position__reports_to').filter(...), but is there any way to avoid making another trip to the database to get the person_set? I tried adding 'position__reports_to__person_set' or just position__reports_to__person to the select_related, but that doesn't seem to change the query. Is this what prefetch_related is for?

如果我使用一个QuerySet，我可以使用position和report_to使用Person.objects连接(并避免第二次访问数据库)。select_related('position'， 'position__reports_to').filter(…)，但是有没有办法避免再次访问数据库以获取person_set呢?我尝试向select_related添加“position__reports_to__person_set”或position__reports_to__person，但这似乎并没有改变查询。这是prefetch_related的意思吗?

I'd like to make a custom manager so that when I do a query to get Person records, I also get their PositionHeirarchy and their manager's Person record without more round trips to the database. This is what I have so far:

我想做一个自定义管理器，这样当我做一个查询来获取人员记录时，我也可以获得他们的职位和他们的经理的个人记录，而不需要更多的往返数据库。这是我目前所拥有的:

class PersonWithManagerManager(models.Manager):
    def get_query_set(self):
        qs = super(PersonWithManagerManager, self).get_query_set()
        return qs.select_related(
            'position',
            'position__reports_to',
        ).prefetch_related(
        )

1 个解决方案

#1

Yes, that is what prefetch_related() is for. It will require an additional query, but the idea is that it will get all of the related information at once, instead of once per Person.

是的，这就是prefetch_related()的用途。它将需要一个额外的查询，但其思想是它将同时获得所有相关信息，而不是每人一次。

In your case:

在你的例子:

qs.select_related('position__report_to')
  .prefetch_related('position__report_to__person_set')

should require two queries, regardless of the number of Persons in the original query set.

应该需要两个查询，不管原始查询集中有多少人。

Compare this example from the documentation:

将此示例与文档进行比较:

>>> Restaurant.objects.select_related('best_pizza')
                      .prefetch_related('best_pizza__toppings')

#1