django - 如何使用注释在多个表中制作反向外键

Question

我有 3 个模型通过外键连接到一个:

class A(models.Model):
    name = models.CharField(max_length=20)

class B(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    amount_b = models.FloatField()

class C(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    amount_c = models.FloatField()

class D(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    amount_d = models.FloatField()

我想使用 Django ORM 基于外键进行 JOIN 查询，以获取每个金额的总和。

在 SQL 中我会做这样的事情:

SELECT 
  a.name, 
  b_sum,
  c_sum,
  d_sum
FROM A AS a
  LEFT JOIN (SELECT b.a, sum(amount_b) as b_sum
              from B as b
              group by b.a) as AB AB.a = a.id
  LEFT JOIN (SELECT c.a, sum(amount_c) as c_sum
              from C as c
              group by a) AS AC ON AC.a = a.id
  LEFT JOIN (SELECT d.a, sum(amount_d) as d_sum
              from D as d
              group by a) AS AD ON AD.a = a.id
WHERE a.name LIKE 'init%'

但我没有在 django ORM 上找到类似的东西。

Answer 1

你可以这样做

q = A.objects.annotate(
    b_sum=Subquery(
        B.objects.filter(a=OuterRef('pk'))
                 .values('a')
                 .annotate(b_sum=Sum('amount_b'))
                 .values('b_sum')
    ),
    c_sum=Subquery(
        C.objects.filter(a=OuterRef('pk'))
                 .values('a')
                 .annotate(c_sum=Sum('amount_c'))
                 .values('c_sum')
    ),
    d_sum=Subquery(
        D.objects.filter(a=OuterRef('pk'))
                 .values('a')
                 .annotate(d_sum=Sum('amount_d'))
                 .values('d_sum')
    )
)

这看起来非常复杂的查询，也不会转换为您提到的相同 sql，但它完成了工作。

这是它生成的 sql。

SELECT "A"."id",
       "A"."name",
       (SELECT SUM(U0."amount_b") AS "b_sum" FROM "B" U0 WHERE U0."a_id" = ("A"."id") GROUP BY U0."a_id") AS "b_sum",
       (SELECT SUM(U0."amount_c") AS "c_sum" FROM "C" U0 WHERE U0."a_id" = ("A"."id") GROUP BY U0."a_id") AS "c_sum",
       (SELECT SUM(U0."amount_d") AS "d_sum" FROM "D" U0 WHERE U0."a_id" = ("A"."id") GROUP BY U0."a_id") AS "d_sum"
FROM "A"