Using subqueries with UPDATE in Django's ORM

user_6074177 0
Viewed 219

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);
django-queryset django-orm django python
2 Answers

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.

user_701910

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
user_137078460
Related