join - Django - joining a nested group_by

Question

I want to aggregate data from 2 tables (A, B) which are linked via a 3rd table (KeyModel). I know about the use of Django's queryset methods 'values' and 'annotate', and managed to create complex queries with it, but this one I got stuck with:

model A
    key = ForeignKey(KeyModel)
    value_1 = IntegerField()

model B
    key = ForeignKey(KeyModel)
    value_2 = IntegerField()

model KeyModel
    <some irrelevant fields>

data - model/ table A
key_id | value_1
4711 | 100

data - model/ table B
key_id | value_2
4711 | 100
4711 | 100
4711 | 100

I like to achieve this result:

-- key_id | sum_value_1 | sum_value_2
-- 4711 | 100 | 300

The following django/ python code generates the wrong SQL:

qs = table_a.objects.filter(key_id=4711)
qs = qs.values('key_id')
qs = qs.annotate(sum_value_1=SUM(value_1))
qs = qs.annotate(sum_value_2=SUM(KeyModel__table_b__value_2))

will generate

SELECT 
    A.key_id,
    SUM(A.value_1) AS sum_value_1,
    SUM(B.value_2) AS sum_value_2
FROM table_a A
JOIN table_b B ON B.key_id=A.key_id
WHERE A.key_id=4711
GROUP BY A.key_id;

But this is not what I need. The join triples the rows from table A, so the result is

key_id | sum_value_1 | sum_value_2
4711 | 300 | 300

A 'slightly' changed SQL is doing what I want:

SELECT 
    A.key_id,
    SUM(A.value_1) AS sum_value_1,
    V.sum_value_2
FROM table_a A
JOIN (
    SELECT
        B.key_id,
        SUM(B.value_2) AS sum_value_2
    FROM table_b B
    GROUP BY B.key_id   
) V ON V.key_id=A.key_id
WHERE A.key_id=4711
GROUP BY A.key_id, V.sum_value_2;

But, how to do that with django queryset methods? (btw: django 2.2) I am pretty sure it is a tiny detail I miss - but after having turned around the various code pieces a number of times I got lost - thanks for any hint.

question from:https://stackoverflow.com/questions/65888963/django-joining-a-nested-group-by

Answer 1

SubQuery is the topic to look for. I solved the issue this way:

sq = table_b.objects.filter(key_id=OuterRef('key_id'))
sq = sq.values('key_id')
sq = sq.annotate(sum_value_2=Sum('value_2', output_field=IntegerField()))

qs = table_a.objects.filter(key_id=4711)
qs = qs.values('key_id')
qs = qs.annotate(sum_value_1=Sum('value_1', output_field=IntegerField()))
qs = qs.annotate(sum_value_2=Subquery(queryset=sq.values('sum_value_2'), output_field=IntegerField()))

It does not produce exactly the SQL I have required above, but it returns the result I desired.