Django F expressions joined field

Question

So I am trying to update my model by running the following:

FooBar.objects.filter(something=True).update(foobar=F('foo__bar'))

but I get the following error:

FieldError: Joined field references are not permitted in this query

if this is not allowed with F expressions...how can I achieve this update?

ticket

given the information in this ticket, I now understand that this is impossible and will never be implemented in django, but is there any way to achieve this update? maybe with some work around? I do not want to use a loop because there are over 10 million FooBar objects, so SQL is much faster than python.

score 68 · Answer 1 · edited Feb 08 '19 at 18:54

68

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

edited Feb 08 '19 at 18:54

fcurella

2,461
2
19
7

answered Jul 27 '18 at 22:10

Georgi Yanchev

1,215
10
10

5

This should be set as the accepted answer. It is very helpful, thank you! – mistiru Nov 09 '18 at 15:56
2

No, it does not work for me. I got this error: django.db.utils.OperationalError: (1093, "You can't specify target table 'xxx' for update in FROM clause") – Dat TT Jan 14 '22 at 10:15
1

I don't think this works with MySQL, because it performs a query that updates a table based on a subquery that references the same table in the FROM clause. – caiolopes Mar 03 '23 at 01:35

score 8 · Answer 2 · edited Sep 16 '20 at 07:44

8

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)

edited Sep 16 '20 at 07:44

Kye

4,279
3
21
49

answered Jan 30 '14 at 06:58

eran

6,731
6
35
52

1

Here's another way to do it: """UPDATE app_foobar b SET mycol = foo.othercol FROM app_foo a WHERE a.id = b.foo_id""". – Patch Rick Walsh Aug 26 '14 at 12:43

score 6 · Answer 3 · answered Dec 29 '18 at 20:31

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

answered Dec 29 '18 at 20:31

lampslave

1,431
1
15
20

3

`pk=OuterRef('bar')` instead of `id=OuterRef('bar_id')` worked nicely for me, and I prefer it because it only uses model's attributes (`bar`) as opposed to Django's internal ones (`bar_id`). – Vedran Šego Jan 30 '20 at 11:54

score 2 · Answer 4 · answered Dec 04 '20 at 14:14

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.

Evgeni Shudzel · Answer 5 · 2023-06-28T08:53:10.673

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)}]")

Django F expressions joined field

ticket

5 Answers5

Linked