Django F expressions joined field

django annotate
joined field references are not permitted in this query
django coalesce
django queryset
django case when
django annotate related field
django group by
django rawsql

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.

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

Query Expressions | Django documentation, An F() object represents the value of a model field or annotated column. The expressions will be converted to strings, joined together with arg_joiner , and then  In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.Let’s say you have a Product class with a price In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.Let’s say you have a Product class with a price field, and you want to increase the pric

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

from django.db import connection
cursor = connection.cursor()
cursor.execute(raw_query)

#14104 (Allow joined field in F()s) – Django, An F() object represents the value of a model field or annotated column. A class attribute that denotes the character used to join the list of expressions together  The actual issue here appears to be that joined F() clauses aren't permitted in update() statements. This is by design; support for joins in update() clauses was explicitly removed due to inherent complications in supporting them in the general case.

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

Making queries | Django documentation, To shorten the query, I want to make a foreign key to A in C, so I don't need to join anymore. In order to achieve that, I put the following code in a migration script: C. When you write the expression F('field') + 1, Django implicitly wraps the 1 in a Value(), allowing simple values to be used in more complex expressions. You will need to use Value() when you want to pass a string to an expression.

Conditional Expressions | Django documentation, Django provides F expressions to allow such comparisons. Instances of F() act as a reference to a model field within a query. introduce any joins needed to access the related object. The first time I ever opened the Django repo was to submit this bug. comment:4 Changed 3 years ago by Tim Graham In the sqlite3/operations.py file referenced in the traceback, it might be enough to check the datetime using django.utils.timezone.is_aware() and skip the timezone.make_aware() call if appropriate.

Django Tips #13 Using F() Expressions, Support for boolean Expression was added. Some examples: >>> from django.​db.models import F, Q, When >>> # String arguments refer to fields; the following​  When you write the expression F('field') + 1, Django implicitly wraps the 1 in a Value(), allowing simple values to be used in more complex expressions. You will need to use Value() when you want to pass a string to an expression. Most expressions interpret a string argument as the name of a field, like Lower('name').

Query Expressions, In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.Let's say you have a Product class  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; ''' from django.db import connection cursor = connection.cursor() cursor.execute(raw_query) share|improve this answer.

Comments
  • This should be set as the accepted answer. It is very helpful, thank you!
  • 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""".