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.

Please use a `ForeignKey` to refer to a parent, *not* a `CharField` that joins on the name. — Willem Van Onsem, Feb 20 '22 at 13:25

Willem Van Onsem · Answer 1 · 2022-02-20T14:22:01.713

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.

We're using a legacy system where data producer cannot insert such a relationship. — th3465, Feb 20 '22 at 14:09
@th3465: you can also let it refer to the `name` of the parent (see edit). In that case of course the database has poor [database normalization](https://en.wikipedia.org/wiki/Database_normalization). — Willem Van Onsem, Feb 20 '22 at 14:22

Performing a Subquery, Sum and Join in django ORM

1 Answers1