四表连接djang

Question

简介

对django来说比较陌生，很好奇如何使用ORM为我的特定用例进行4路连接。你知道吗

Django车型

class Foo( models.Model ):
  attr1 = ...

class Bar( models.Model ):
  foo = models.ForeignKey ( Foo )
  attr2 = ...

class BarOneToOne( models.Model ):
  bar = models.ForeignKey ( Bar ) 
  attr3 = ...

class BarManyToOne( models.Model ):
  bar = models.ForeignKey ( Bar )
  attr4 = ...

问题

想要一个BarManyToOne列表和相关的BarOneToOne、Bar和Foo数据。希望根据Fooid筛选。你知道吗

示例

数据：

Foo                     Bar
--------------        -----------------------
| id | attr1 |        | id | attr2 | foo_id |
--------------        -----------------------
  1    apple           101   larry      1
  2    orange          102   bob        2
  3    pear            103   sue        3
  4    grape           104   laura      1
                       105   nancy      4

BarOneToOne            BarManyToOne
------------------     --------------------
| bar_id | attr3 |     | bar_id |  attr4  |
------------------     --------------------
   101      dog           101     cinnamon
   103      mouse         104     thyme
   104      cat           103     garlic
                          104     sea salt
                          101     chili powder
                          103     paprika

期望结果：

 --------------------------------------
 | apple | larry | dog |   cinnamon   |
 --------------------------------------
 | apple | laura | cat |    thyme     |
 --------------------------------------
 | apple | laura | cat |   sea salt   |
 --------------------------------------
 | apple | larry | dog | chili powder |
 --------------------------------------

SQL等价物

SELECT f.attr1, b.attr2, bmto.attr3, bmtoa.attr4
FROM foo f, bar b, bar_one_to_one boto, bar_many_to_one bmto
WHERE bmto.bar_id = boto.bar_id AND b.id = bmto.bar_id
AND b.foo_id = f.id AND f.id = ?;

杂项

到目前为止，我在文档中看到了select_related，这感觉非常有用。还有“方法”。感觉碎片就在这里，但还没来得及拼凑起来。你知道吗

就性能/大小而言，Foo、Bar和BarOneToOne表都将很小（每个表不到500行，甚至可能每个表不到100行）。巴曼酮桌子会更大。你知道吗

提前谢谢。你知道吗

Answer 1

您需要根据您的参照系为您的模型设置相关的名称。有很多方法可以做到这一点，但为此，我将使用BarManyToOne表作为主表。你知道吗

class Foo( models.Model ):
    attr1 = ...

class Bar( models.Model ):
    foo = models.ForeignKey ( Foo, related_name='foo_bars')
    attr2 = ...

class BarOneToOne( models.Model ):
    bar = models.ForeignKey ( Bar, related_name='bar_baronetoones' ) 
    attr3 = ...

class BarManyToOne( models.Model ):
    bar = models.ForeignKey ( Bar, related_name='bar_barmanytoones' )
    attr4 = ...

对于上面所示的模型设置，这将是您需要调用的代码，以获得等效的SQL查询。你知道吗

BarManyToOne.objects.all().select_related(
    'bar',                             # Joins the three other tables
    'bar__foo',                        # with BarManyToOne serving as
    'bar__bar_baronetoones'            # the main table
).filter(                              # Your (non join condition) WHERE clauses
    bar__foo_id=<your value here>      # f.id = ?
).values(                              # The columns you want
    'bar__foo__attr1',                 # f.attr1
    'bar__attr2',                      # b.attr2
    'bar__bar_baronetoones__attr3',    # boto.attr3
    'attr4'                            # bmto.attr4
)

你需要仔细阅读related_name、the making queries section和F() expressions。你知道吗