Update column in Django with child columns

Question

I have 2 models Parent, Child

class Parent(models.Model):
    id = Base64UUIDField(primary_key=True, editable=False)
    cost = models.DateTimeField(default=None, blank=True, null=True)

class Child(models.Model):
    id = Base64UUIDField(primary_key=True, editable=False)
    cost = models.DateTimeField(default=None, blank=True, null=True)
    parent = models.ForeignKey(Parent, related_name= "children", related_query_name= "child")

I need to populate cost column of Parent objects to maximum cost of all children of that parent

I have tried to annotate to a new column new_cost, and its works.

parents.annotate(new_cost=Max('child__cost'))

But I need to populate values to existing column cost. Tried something like this, but not working.

parents.update(cost=Max('child__cost'))

I'm not sure that you should have a `cost` field in the `Parent`, normally it is better to just annotate it when needed. — Willem Van Onsem, Jul 08 '21 at 21:26

score 2 · Answer 1 · answered Jul 08 '21 at 21:33

Probably that can be achieved with a Subquery expression:

from django.db.models import OuterRef, Subquery

Parent.objects.update(
    cost=Subquery(Child.objects.filter(
        parent_id=OuterRef('pk')
    ).values('cost').order_by('-cost')[:1])
)

I would however advise to simply use .annotate(…) when you need the largest cost of the related Childs.

score 0 · Answer 2 · answered Jul 08 '21 at 21:36

0

Use aggregate againist to annotate

test = parents.aggregate(new_cost=Max('child__cost'))
parents.update(cost=test["new_cost"])

answered Jul 08 '21 at 21:36

Reza Heydari

1,146
1
6
10

1

This will set for *all* `Parent`s the `cost` to the maximum of all children of all `Parent`s, likely the OP want to set this *per* `Parent`. – Willem Van Onsem Jul 08 '21 at 21:46
AttributeError: 'dict' object has no attribute 'aggregate'. gettig this error – PARRE PRITHVI Jul 08 '21 at 21:47
@PARRE PRITHVI I think @Willem Van Onsem is right its not a best practice to have ```cost``` for two models.. you can use annotate or have a ```@propety``` cost in parent model – Reza Heydari Jul 08 '21 at 21:56
But if you want do it, show how you get `parents` – Reza Heydari Jul 08 '21 at 22:01

Update column in Django with child columns

2 Answers2