Using subqueries with UPDATE in Django's ORM

Question

Say I have two models, which I'll call ModelA and ModelB. Both models have a few fields in common, (represented by field_one, field_two, and field_three). Additionally, ModelB has a foreign key to ModelA.

class ModelA(Model):
    field_one = models.IntegerField()
    field_two = models.TextField()
    field_three = models.BooleanField()

class ModelB(Model):
    field_one = models.IntegerField()
    field_two = models.TextField()
    field_three = models.BooleanField()
    model_a = models.ForeignKey(ModelA, on_delete=models.CASCADE)

I need to update all instances of ModelB to update the field's values to the values of the associated ModelA instances. I need to do this operation entirely in the database, without needing to instantiate any model instances (NOT using .save() or bulk_update()).

I know how I can accomplish this in PostgreSQL using subqueries:

UPDATE model_b SET (field_one, field_two, field_three) =
    (SELECT field_one, field_two, field_three FROM model_a
     WHERE model_b.model_a_id = model_a.id);

How can I express the above query in Django's ORM?

This is the closest I have been able to get:

ModelB.objects.update(
    field_one=Subquery(ModelA.objects.filter(id=OuterRef('model_a_id')).values(field_one)[:1]),
    field_two=Subquery(ModelA.objects.filter(id=OuterRef('model_a_id')).values(field_two)[:1]),
    field_three=Subquery(ModelA.objects.filter(id=OuterRef('model_a_id')).values(field_three)[:1])
})

However, this results in a subquery for every single field:

UPDATE model_b SET
    field_one = (SELECT model_a.field_one FROM model_a WHERE model_a.id = model_b.model_a_id LIMIT 1),
    field_two = (SELECT model_a.field_two FROM model_a WHERE model_a.id = model_b.model_a_id LIMIT 1),
    field_three = (SELECT model_a.field_three FROM model_a WHERE model_a.id = model_b.model_a_id LIMIT 1);

The first query you have could have been supported by this: `ModelB.objects.update(field_one=F('modela__field_one'), field_two=F('modela__field_two'), field_three=F('modela__field_three')) `, but unfortunately, django does not support this. See [`here`](https://stackoverflow.com/questions/21439031/django-f-expressions-joined-field) and [`here`](https://code.djangoproject.com/ticket/14104) — Brian Destura, Jul 17 '21 at 04:17
So if you want to do it in one query, subquery is the only option I think. — Brian Destura, Jul 17 '21 at 04:23

score 3 · Accepted Answer · answered Jul 19 '21 at 20:56

Unfortunately the ORM doesn't support annotations spreading multiple columns and I'm not aware of a feature request for it.

If you want to stick to the ORM you'll have to take the possible performance hit (maybe PostgreSQL is smart enough to use only a single one; EXPLAIN would tell) otherwise you'll have to switch to .raw SQL.

score -2 · Answer 2 · edited Jul 20 '21 at 06:58

-2

by passing modelA as an argument in the modelB class arguments, ModelB and modelC will inherit of every field of modelA

class ModelA(Model):
    field_one = models.IntegerField()
    field_two = models.TextField()
    field_three = models.BooleanField()

class ModelB(ModelA):
    pass

class ModelC(ModelA):
    pass

edited Jul 20 '21 at 06:58

Brian Destura

11,487
3
18
34

answered Jul 16 '21 at 20:40

Drayen Dörff

93
6

1

Thanks, but this isn't really relevant to the particular question I am asking. The table structure is non-negotiable here. – Ryan Jul 16 '21 at 20:47

Using subqueries with UPDATE in Django's ORM

2 Answers2