Update model using subquery with reference to outer fields

Using Django Using the ORM
1

Hi,

I was wondering whether it is possible to rewrite a for loop to update a model.

So I have models which are (simplified) like this:

class Text(Model):
    key = CharField(primary_key=True, max_length=100)
    part_of_journal = ForeignKey(Journal, null=True, on_delete=CASCADE)

class Journal(Model):
    identifier = CharField(primary_key=True, max_length=100)

Both key and identifier are similar but not the same. Now I want to assign the Text to the right Journal. It’s possible in a for loop using

for text in Text.objects.all():
    text.part_of_journal = Journal.objects.filter(identifier='streams/' + text.key[:4]).first()
    text.save()

I was wondering whether it is possible to do the same using an update with a subquery like:

Text.objects.all()\
            .annotate(converted_key=Concat(V('streams/'), Substr('key', 1, 5)))\
            .update(part_of_journal=Journal.objects.filter(identifier=OuterRef('converted_key')).first())

However, all the different versions I’ve tried always fail with referencing the converted_key.

Does anyone know whether/how that is possible?

2

Is it possible? No, not this way.

The update function applies to a queryset, it does not create individual updates for each row. (See the docs at Updating multiple objects at once for specifics on the limitations of the update function.)

If you’re looking to reduce the number of saves being performed, you may find bulk_update helpful. You’re still going to iterate over the objects, but only issuing one command to perform the save.

3

I would have thought this was possible. There’d be a annotate that has a subquery on Journal that does the lookup using the identifier comparison, but it returns the id. Then the update uses F(“annotated_journal_id”) for the value.

1 Like
4

Yep, that works! (Just checked it)

Move the Journal query to the annotation to create the new value and then use the update to update based upon the annotated value.

5

Thanks for the replies!

I actually just figured out another way to do this: Not using annotate at all but using a Subquery with OuterRef(key) as part of the update function.

This was an interesting deep-dive into the django ORM

1 Like
6

I didn’t know that was possible, TIL!

1 Like
7

Can you show what that looks like using your example models please? Thanks.

8

Sure, this is what I ended up with:

subquery = Journal.objects.filter(
    identifier=Concat(V('streams/'), Substr(OuterRef('key'), 1, 5))
)
Text.objects.update(
    part_of_journal=Subquery(
        subquery.values('identifier')[:1],
        output_field=models.CharField()
    )
)
2 Likes