3

I have a query like: Foo.objects.extra(select={'extra_column':'SELECT ...'}), and I want to filter on the extra_column, but I cannot get this alias on filter. I am thinking of using annotate(), but I don't know annotate() can using on selectclause, all the tutorial is on SUM etc. I also tried HAVING clause, but it seems that Django doesn't support HAVING on extra().

Any suggestions will be very appreciated.

Edit:

Django 1.6, MySQL

I am query on some complicated condition, so I must using extra() to write the subquery. But I cannot using filter() to access the alias of the subquery. If I turned to annotate(), which can be accessed in later filter(), but seems that it only works with SUM, MAX, MIN, AVG, thus I cannot use it on my custom SELECT subquery.

I read the similar question here: Django ORM: Filter by extra attribute. It said that when query on a alias on MySQL, you need to use HAVING clause.

I don't know if this issue is also on other SQL backends like PostgreSQL, or on higher version of Django. Maybe there is no built-in solution on this question in Django1.6 with MySQL?

Community
  • 1
  • 1
Fred Wu
  • 908
  • 1
  • 9
  • 14
  • Can you explain what you trying to do with the extra query? Maybe it could be solved by doing joins or even a simple annotate. And also, which Django version are you using? – Blackeagle52 Dec 22 '14 at 10:40

1 Answers1

2

The as the correct marked answer, of the similar question, mentions; it is not possible to use a filter (because filter() only inspects model definitions). It suggest to use another extra, containing a where clause.

So something like;

Foo.objects\
    .extra(select={'extra_column':'SELECT ...'})\
    .extra(where=["extra_column = %s"], params=[value])

As of Django 1.7 you can work with lookups:
https://docs.djangoproject.com/en/1.7/howto/custom-lookups/

PADYMKO
  • 4,217
  • 2
  • 36
  • 41
Blackeagle52
  • 1,956
  • 17
  • 16
  • 2
    Actually extra will render where clause in SQL. But in MySQL, you need HAVING here. So I finally used the raw SQL. – Fred Wu Dec 23 '14 at 05:39
  • 2
    This example does not work. It raises `OperationalError: (1054, "Unknown column 'extra_column' in 'where clause'")` on MySQL database. You cannot use fields generated using `extra` in another `extra` clause. – niekas Mar 13 '19 at 11:58