[Django] #21738: models.F does not accept fields generated via QuerySet.extra
Django
----------------------------------------------+--------------------
Reporter: tonnzor | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
I found that models.F has a limited usage -- it cannot be used for fields
added manually via QuerySet.extra(select={...})
Sample use case:
{{{
User.objects.extra(select={'new_is_active': '(CASE WHEN id > 100 THEN 1
ELSE 0 END)'}).update(is_active=models.F('new_is_active'))
}}}
Probably it was intended but if F supports extra fields -- it would be
really powerful tool. Otherwise you have to fall to raw SQL.
Also I see at least one way to convert it to SQL -- so there should be a
way to implement it:
{{{
UPDATE auth_user
set is_active = (CASE WHEN id > 100 THEN 1 ELSE 0 END)
}}}
Here is the example:
{{{
git clone https://github.com/django/django.git
cd django
cp django/bin/django-admin.py .
./django-admin.py startproject ttest
cd ttest
ln -s ../django
./manage.py syncdb --noinput
echo "from django.contrib.auth.models import *;
User.objects.extra(select={'new_is_active': '(CASE WHEN id > 100 THEN 1
ELSE 0 END)'}).update(is_active=models.F('new_is_active'))" | ./manage.py
shell
Python 2.7.5 (default, Aug 25 2013, 00:04:04)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.0.68)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/tonnzor/build/django/ttest/django/db/models/query.py", line
577, in update
rows = query.get_compiler(self.db).execute_sql(None)
File
"/Users/tonnzor/build/django/ttest/django/db/models/sql/compiler.py", line
958, in execute_sql
cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
File
"/Users/tonnzor/build/django/ttest/django/db/models/sql/compiler.py", line
752, in execute_sql
sql, params = self.as_sql()
File
"/Users/tonnzor/build/django/ttest/django/db/models/sql/compiler.py", line
932, in as_sql
val = SQLEvaluator(val, self.query, allow_joins=False)
File
"/Users/tonnzor/build/django/ttest/django/db/models/sql/expressions.py",
line 14, in __init__
self.expression.prepare(self, query, allow_joins)
File
"/Users/tonnzor/build/django/ttest/django/db/models/expressions.py", line
149, in prepare
return evaluator.prepare_leaf(self, query, allow_joins)
File
"/Users/tonnzor/build/django/ttest/django/db/models/sql/expressions.py",
line 62, in prepare_leaf
query.get_initial_alias(), self.reuse)
File "/Users/tonnzor/build/django/ttest/django/db/models/sql/query.py",
line 1375, in setup_joins
names, opts, allow_many)
File "/Users/tonnzor/build/django/ttest/django/db/models/sql/query.py",
line 1302, in names_to_path
"Choices are: %s" % (name, ", ".join(available)))
FieldError: Cannot resolve keyword 'new_is_active' into field. Choices
are: date_joined, email, first_name, groups, id, is_active, is_staff,
is_superuser, last_login, last_name, logentry, password, user_permissions,
username
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/21738>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: tonnzor | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by smeatonj):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
I think this ticket is somewhat related to
https://code.djangoproject.com/ticket/14030
Extra doesn't do anything smart. It stashes the raw value into a
dictionary, and is simply added to the query on execution. Ideally, extra
wouldn't be used at all. Instead, you should be able to:
User.objects.annotate(new_is_active=SQL('CASE WHEN id > 100 THEN 1 ELSE 0
END')).update(is_active=models.F('new_is_active'))
I don't think there's any good reason to improve .extra() behaviour, as it
is mostly a workaround for issues in other parts of the ORM which should
be fixed instead.
--
Ticket URL: <https://code.djangoproject.com/ticket/21738#comment:1>
Django
Reporter: tonnzor | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: master
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by akaariai):
* status: new => closed
* resolution: => wontfix
Comment:
I agree that adding more hacks for .extra() isn't a good idea. So, I'll
wontfix this.
We could possibly make .extra() to automatically do
.annotate(new_is_active=SQL('CASE WHEN id > 100 THEN 1 ELSE 0 END')), but
this is pending on getting .annotate() support for that.
--
Ticket URL: <https://code.djangoproject.com/ticket/21738#comment:2>
Django
Reporter: tonnzor | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: master
(models, ORM) | Resolution: wontfix
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by tonnzor):
OK, I will track #14030 then -- that would solve my problem as well.
--
Ticket URL: <https://code.djangoproject.com/ticket/21738#comment:3>