Monday, 19 July 2021

Using subqueries with UPDATE in Django's ORM

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);


from Using subqueries with UPDATE in Django's ORM

No comments:

Post a Comment