How can I get computed elements of a table in a django queryset?

Short answer:If you create an aliased (or computed) column using extra(select=...)then you cannot use the aliased column in a subsequent call to filter().Also, as you've discovered, you can't use the aliased column in later calls toextra(select=...) or extra(where=...).

An attempt to explain why:

For example:

qs = MyModel.objects.extra(select={'alias_col': 'title'})#FieldError: Cannot resolve keyword 'alias_col' into field...filter_qs = qs.filter(alias_col='Camembert')#DatabaseError: column "alias_col" does not existextra_qs = qs.extra(select={'another_alias': 'alias_col'})

filter_qs will try to produce a query like:

SELECT (title) AS "alias_col", "myapp_mymodel"."title"FROM "myapp_mymodel"WHERE alias_col = "Camembert";

And extra_qs tries something like:

SELECT (title) AS "alias_col", (alias_col) AS "another_alias",        "myapp_mymodel"."title"FROM "myapp_mymodel";

Neither of those is valid SQL. In general, if you want to use a computed column's alias multiple times in the SELECT or WHERE clauses of query you actually need to compute it each time. This is why Roman Pekar's answer solves your specific problem - instead of trying to compute chosen_date once and then use it again later he computes it each time it's needed.

You mention Annotation/Aggregation in your question. You can use filter() on the aliases created by annotate() (so I'd be interested in seeing the similar errors you're talking about, it's been fairly robust in my experience). That's because when you try to filter on an alias created by annotate, the ORM recognizes what you're doing and replaces the alias with the computation that created it.

So as an example:

qs = MyModel.objects.annotate(alias_col=Max('id'))qs = qs.filter(alias_col__gt=0)

Produces something like:

SELECT "myapp_mymodel"."id", "myapp_mymodel"."title",        MAX("myapp_mymodel"."id") AS "alias_col"FROM "myapp_mymodel"GROUP BY "myapp_mymodel"."id", "myapp_mymodel"."title"HAVING MAX("myapp_mymodel"."id") > 0;

Using "HAVING MAX alias_col > 0" wouldn't work.

I hope that's helpful. If there's anything I've explained badly let me know and I'll see if I can improve it.

Well here're some workarounds

1. In your particular case you could do it with one extra:

if use_date_due:    sum_qs = sum_qs.extra(select={                          'year': 'EXTRACT(year FROM coalesce(date_due, date))',                          'month': 'EXTRACT(month FROM coalesce(date_due, date))',                          'is_paid':'date_paid IS NOT NULL'                        })

2. It's also possible to use plain python to get data you need:

for x in sum_qs:    chosen_date = x.date_due if use_date_due and x.date_due else x.date    print chosen_date.year, chosen_date.month

or

[(y.year, y.month) for y in (x.date_due if use_date_due and x.date_due else x.date for x in sum_qs)]

3. In the SQL world this type of calculating new fields is usually done by uing subquery or common table expression. I like cte more because of it's readability. It could be like:

with cte1 as (    select        *, coalesce(date_due, date) as chosen_date    from polls_invoice)select    *,    extract(year from chosen_date) as year,    extract(month from chosen_date) as month,    case when date_paid is not null then 1 else 0 end as is_paidfrom cte1

you can also chain as many cte as you want:

with cte1 as (    select        *, coalesce(date_due, date) as chosen_date    from polls_invoice), cte2 as (    select        extract(year from chosen_date) as year,        extract(month from chosen_date) as month,        case when date_paid is not null then 1 else 0 end as is_paid    from cte2)select    year, month, sum(is_paid) as paid_countfrom cte2group by year, month

so in django you can use raw query like:

Invoice.objects.raw('     with cte1 as (        select            *, coalesce(date_due, date) as chosen_date        from polls_invoice    )    select        *,        extract(year from chosen_date) as year,        extract(month from chosen_date) as month,        case when date_paid is not null then 1 else 0 end as is_paid    from cte1')

and you will have Invoice objects with some additional properties.

4. Or you can simply substitute fields in your query with plain python

if use_date_due:    chosen_date = 'coalesce(date_due, date)'else:     chosen_date = 'date'year = 'extract(year from {})'.format(chosen_date)month = 'extract(month from {})'.format(chosen_date)fields = {'year': year, 'month': month, 'is_paid':'date_paid is not null'}, 'chosen_date':chosen_date)sum_qs = sum_qs.extra(select = fields)

Would this work?:

from django.db import connection, transactioncursor = connection.cursor()sql = """    SELECT         %s AS year,         %s AS month,        date_paid IS NOT NULL as is_paid    FROM (        SELECT            (CASE WHEN date_due IS NULL THEN date_due ELSE date END) AS chosen_date, *        FROM            invoice_invoice    ) as t1;    """ % (connection.ops.date_extract_sql('year', 'chosen_date'),           connection.ops.date_extract_sql('month', 'chosen_date'))# Data retrieval operation - no commit requiredcursor.execute(sql)rows = cursor.fetchall()

I think it's pretty save both CASE WHEN and IS NOT NULL are pretty db agnostic, at least I assume they are, since they are used in django test in raw format..