How to make a reverse Foreign Key in multiple tables with annotate

Question

I have 3 models connected to one by a Foreign Key:

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()

And I want to make a JOIN query based on the foreign keys using Django ORM to get the sum of each amount.

In SQL I would make something like:

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%'

But I didn't find something like that on django ORM.

Answer 1

You can do this

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')
    )
)

It seems pretty complex query and also does not translate to same sql you mentioned in question, but it does the job.

Here is sql it generates.

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"