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

Might be related to ticket #8784.

Last edited 3 years ago by hcarvalhoalves (previous) (diff)

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

I've updated the patch with some cosmetic tweaks as well as to merge cleanly to master (confirmed all tests pass as well). Would like someone more familiar with the code to +1 before it gets committed.

https://github.com/django/django/pull/1242

Version 0, edited 2 years ago by timo (next)

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