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

[Henrique C. Alves]

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.

[Henrique C. Alves]

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.

[Aymeric Augustin]

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

[fhahn]

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.

[Tim Graham]

<commented on incorrect ticket>

[Tim Graham]

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

[Tim Graham]

Patch no longer merges cleanly.

[Tim Graham]

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