Code

Opened 20 months ago

Last modified 5 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.

Attachments (0)

Change History (7)

comment:1 Changed 20 months ago by hcarvalhoalves

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

Might be related to ticket #8784.

Last edited 20 months ago by hcarvalhoalves (previous) (diff)

comment:2 Changed 19 months 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 17 months 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 13 months 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 13 months ago by timo (next)

comment:5 Changed 13 months ago by timo

  • Triage Stage changed from Ready for checkin to Accepted

comment:6 Changed 9 months ago by timo

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

comment:7 Changed 5 months ago by timo

  • Patch needs improvement set

Patch no longer merges cleanly.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.