Assume the following tables:
class Table1(models.Model):
Column1 = models.IntegerField()
class Table2(models.Model):
Column2 = models.IntegerField()
class Table3(models.Model):
Table1 = models.ForeignKey(Table1, null=False, on_delete=models.CASCADE)
Table2 = models.ForeignKey(Table2, null=False, on_delete=models.CASCADE)
Column3 = models.IntegerField()
class Table4(models.Model):
Table1 = models.ForeignKey(Table1, null=False, on_delete=models.CASCADE)
Table3 = models.ForeignKey(Table3, null=False, on_delete=models.CASCADE)
Column4 = models.IntegerField()
This annotate expression returns right answer:
print(Table1.objects.annotate(Exp1=Sum(
Case(
When(table3__Table2__Column2__in=[2, 3],
then=F('table3__Column3')),
default=Value(0)
),
)).values('Exp1'))
That is:
<QuerySet [{'Exp1': 96}]>
And I need to define another annotate expression as below:
print(Table1.objects.annotate(Exp2=Sum(
Case(
When(table4__Table3__Table2__Column2=3,
then=F('table4__Column4')),
default=Value(0)
),
)).values('Exp2'))
Again the result is correct:
<QuerySet [{'Exp2': 0}]>
Finally, I want to combine these two in one command:
print(Table1.objects.annotate(Exp1=Sum(
Case(
When(table3__Table2__Column2__in=[2, 3],
then=F('table3__Column3')),
default=Value(0)
),
), Exp2=Sum(
Case(
When(table4__Table3__Table2__Column2=3,
then=F('table4__Column4')),
default=Value(0)
),
)).values('Exp1', 'Exp2'))
But unfortunately the result is not correct:
<QuerySet [{'Exp2': 0, 'Exp1': 480}]>