Opened 3 years ago

Last modified 18 months ago

#19434 new Bug

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

Reported by: hcarvalhoalves Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
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 (7)

comment:1 Changed 3 years ago by hcarvalhoalves

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 3 years ago by hcarvalhoalves (next)

comment:2 Changed 3 years ago by aaugustin

  • Triage Stage changed from Unreviewed to Accepted

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

comment:3 Changed 2 years ago by fhahn

  • Cc flo@… added
  • Has patch set
  • Version changed from 1.4 to master

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 Changed 2 years ago by timo

  • Triage Stage changed from Accepted to Ready for checkin

<commented on incorrect ticket>

Last edited 2 years ago by timo (previous) (diff)

comment:5 Changed 2 years ago by timo

  • Triage Stage changed from Ready for checkin to Accepted

comment:6 Changed 22 months ago by timo

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

comment:7 Changed 18 months ago by timo

  • Patch needs improvement set

Patch no longer merges cleanly.

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