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