[Django]-Django F expressions joined field

71👍

Django 1.11 adds supports for subqueries. You should be able to do:

from django.db.models import Subquery, OuterRef

FooBar.objects.filter(something=True).update(
    foobar=Subquery(FooBar.objects.filter(pk=OuterRef('pk')).values('foo__bar')[:1])
)

7👍

Why don’t use raw sql here:
Based on this, it will be something like

from django.db import connection

raw_query = '''
update app_foobar set app_foobar.foobar = 
(select app_foo.bar from app_foo where app_foo.id = app_foobar.foo_id) 
where app_foobar.something = 1; 
'''

cursor = connection.cursor()
cursor.execute(raw_query)
👤eran

6👍

This is the implementation of Georgi Yanchev’s answer for two models:

class Foo(models.Model):
    bar = models.ForeignKey(Bar)

Foo.objects \
    .filter(foo_field_1=True) \
    .update(foo_field_2=Subquery(
        Bar.objects \
            .filter(id=OuterRef('bar_id')) \
            .values('bar_field_1')[:1]))

2👍

For anyone wanting a simpler way to do this and not having the case of huge set of objects, below snippet should work just fine:

for fooBar in FooBar.objects.filter(something=True):
    fooBar.foobar = fooBar.foo.bar
    fooBar.save(update_fields=['foobar'])

For a regular use-cases, this should not present much of a performance difference, especially if being run as part of a data migration.
You can, optionally, also use select_related if needed to further optimize.

0👍

And there is another way using bulk_update() (new in Django 2.2) and optimized for a large number of records:

import time
from itertools import islice

batch_size = 2000
objs = FooBar.objects.filter(something=True).annotate(_foobar=F('foo__bar')).iterator(batch_size)
while True:
    ts = time.time()
    batch = list(islice(objs, batch_size))
    if not batch:
        break
    for i in batch:
        i.foobar = i._foobar
    FooBar.objects.bulk_update(batch, ["foobar"])
    print(f"{time.time() - ts:.03f}s [{len(batch)}]")

Leave a comment