[Answered ]-Performing a Subquery, Sum and Join in django ORM

by

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.

👤willeM_ Van Onsem

Leave a comment