Opened 12 years ago

Closed 9 years ago

#19434 closed Bug (wontfix)

Some QuerySet methods not aware of fields added on "extra"

Reported by: Henrique C. Alves Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: QuerySet.extra
Cc: flo@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

Some examples to illustrate the issue:

(Pdb) CUSTOM_SQL = "SELECT some_function(some_field) FROM some_table"

# This works
(Pdb) qs = qs.extra(select={'some_alias': CUSTOM_SQL}, order_by=['some_alias'])

# This fails
(Pdb) qs.filter(some_alias__gt=0)
*** FieldError: Cannot resolve keyword 'some_alias' into field. Choices are: ...

# This works
(Pdb) qs.values()
[{'some_alias': 1}, {'some_alias': 2}]

# This fails
(Pdb) qs.values('some_alias')
*** FieldError: Cannot resolve keyword 'some_alias' into field. Choices are: ...

There's a situation where half of the API supports extra queries correctly, the other half fails with a FieldError, and there's no clear indication to what should be the right behavior.

Change History (8)

comment:1 by Henrique C. Alves, 12 years ago

Could be worked around if the ORM supported a HAVING clause, so filter et al wouldn't need to be aware of aliases, but ticket #8784 asking for it's inclusion was marked WONTFIX. I have no idea what the solution could be then.

Version 0, edited 12 years ago by Henrique C. Alves (next)

comment:2 by Aymeric Augustin, 12 years ago

Triage Stage: UnreviewedAccepted

Yes, it isn't possible to filter on a field added in a .extra(select={...}). I've hit this problem too.

comment:3 by fhahn, 12 years ago

Cc: flo@… added
Has patch: set
Version: 1.4master

I've started a patch that adds support for filtering fields added by extra and created a pull request: https://github.com/django/django/pull/735

I think something similar has been done to provide support for filtering annotations/aggregations

qs.extra(select={'num_plus_one': 'num + 1'}
qs.filter(num_plus_one=2)

will result in following query:

SELECT (num+1) AS "num_plus_on" ... 
WHERE (num+1) = 2 ....

In order to avoid quoting of the lvalue of the term in the WHERE part I modified WhereNode.add to accept a QueryWrapper (contains the extra sql). There's probably a much better way to add an unqouted lvalue to the WhereNode, any suggestions/feedback are very appreciated.

qs.values('some_alias') worked for me (without modification), but I've added a test to check it.

comment:4 by Tim Graham, 11 years ago

Triage Stage: AcceptedReady for checkin

<commented on incorrect ticket>

Last edited 11 years ago by Tim Graham (previous) (diff)

comment:5 by Tim Graham, 11 years ago

Triage Stage: Ready for checkinAccepted

comment:6 by Tim Graham, 11 years ago

#21159 reported the inability to distinct() on extra columns.

comment:7 by Tim Graham, 11 years ago

Patch needs improvement: set

Patch no longer merges cleanly.

comment:8 by Tim Graham, 9 years ago

Keywords: QuerySet.extra added
Resolution: wontfix
Status: newclosed

We are no longer fixing bugs with QuerySet.extra() per discussion on django-developers.

Note: See TracTickets for help on using tickets.
Back to Top