[FIXED] how to join multiple table sum in single query with foreign key Django ORM

Issue

Here i giving table Structure:

class MainTable(models.Model):
     code = models.CharField(max_length=25)
     qty = models.FloatField(default=0)


class Table1(models.Model):
     code = models.ForeignKey(MainTable, on_delete=models.CASCADE, related_name='table_one')
     date = models.DateField()
     qty = models.FloatField(default=0)


class Table2(models.Model):
     code = models.ForeignKey(MainTable, on_delete=models.CASCADE, related_name='table_two')
     date = models.DateField()
     qty = models.FloatField(default=0)

class Table3(models.Model):
     code = models.ForeignKey(MainTable, on_delete=models.CASCADE, related_name='table_three')
     date = models.DateField()
     qty = models.FloatField(default=0)

I want this type of table:

________________________________________________________
| Code |   qty   | table1_sum| table2_sum | table3_sum |
---------------------------------------------------------
|code1 |  5000   |    2000   |    3000    |    4000    |
---------------------------------------------------------
|code2 |  5000   |    2000   |    3000    |    4000    |
---------------------------------------------------------
|code3 |  5000   |    2000   |    3000    |    4000    |
--------------------------------------------------------

I am traying this query but it does not give proper value:

query = MainTable.objects.all().annotate(table1=(Sum('table_one__qty')),table12=(Sum('table_two__qty')),table3=(Sum('table_three__qty')))

In this query, table one gives the actual value but the other table gives the wrong value. need proper query.

Solution

Change as follow models.py

class MainTable(models.Model):
    code = models.CharField(max_length=25)
    qty = models.IntegerField(default=0)

    def table1_sum(self):
        return self.table_one.aggregate(Sum('qty'))

    def table2_sum(self):
        return self.table_two.aggregate(Sum('qty'))

    def table3_sum(self):
        return self.table_three.aggregate(Sum('qty'))

add custom template_tag: dict_key.py

from django import template

register = template.Library()


@register.filter
def first_item(item):
    total = item['qty__sum']
    if total:
        return total
    return '0'

In template:

{% load dict_key %}

{% for item in object_list %}
    <tr>
        <td>{{ item.code }}</td>
        <td>{{ item.qty }}</td>
        <td>{{ item.table1_sum|first_item }}</td>
        <td>{{ item.table2_sum|first_item }}</td>
        <td>{{ item.table3_sum|first_item }}</td>
    </tr>
{% endfor %}

Answered By - nikolas