Performing a Subquery, Sum and Join in django ORM

Question

I have 2 django models which aren't linked by ForeignKey due to legacy system.

class Parent(model):
    name -> CharField()

class Child(model)
    parent_name -> CharField()
    cost        -> IntegerField()

I want to achieve a left join which gives me all parent columns along with sum of cost column from children. SQL in postgres translates to

select parent.name, sum(child.cost) as cost from parent join child on parent.name = child.parent_name group by parent.name;

Is there any way to achieve this with django ORM

I have tried a lot of things but https://code.djangoproject.com/ticket/28296 might be what is blocking.

Answer 1

Please use a ForeignKey to refer to a parent, not a CharField that joins on the name. This will guarantee referential integrity, avoid data duplication and makes it more convenient to query in Django.

You thus define the models as:

class Parent(models.Model):
    name = models.CharField(max_length=128)

class Child(models.Model):
    parent = models.ForeignKey(
        Parent,
        on_delete=models.CASCADE
    )
    cost = models.IntegerField()

or if the name of the Parent is unique, you can let it refer to the name:

class Parent(models.Model):
    name = models.CharField(max_length=128, unique=True)

class Child(models.Model):
    parent = models.ForeignKey(
        Parent,
        to_field='name',
        db_column='parent_name',
        on_delete=models.CASCADE
    )
    cost = models.IntegerField()

then you can .annotate(…) [Django-doc] the Parent model with:

from django.db.models import Sum

Parent.objects.annotate(
    cost=Sum('child__cost')
)

the Parent objects that arise from this queryset will have an extra attribute .cost that will contain the sum of the costs of the Childs that point to that Parent.