Code

Opened 17 months ago

Last modified 2 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 17 months ago by hcarvalhoalves

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

Might be related to ticket #8784.

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

comment:2 Changed 17 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 14 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 11 months ago by timo

  • Triage Stage changed from Accepted to Ready for checkin

<commented on incorrect ticket>

Last edited 11 months ago by timo (previous) (diff)

comment:5 Changed 11 months ago by timo

  • Triage Stage changed from Ready for checkin to Accepted

comment:6 Changed 6 months ago by timo

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

comment:7 Changed 2 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.