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

Question

I'm trying to use django's queryset API to emulate the following query: ``` SELECT EXTRACT(year FROM chosen_date) AS year, EXTRACT(month FROM chosen_date) 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; ``` The idea is mainly that in certain situations, I'd rather use the `date_due` column rather than the `date` column in some situations, but that , since `date_due` is optional, I sometimes have to use `date` as a fallback anyways, and create a computed column `chosen_date` to not have to change the rest of the queries. Here was a first stab I did at emulating this, I was unable to really see how to properly due the null test with the base api so I went with `extra`: ``` if(use_date_due): sum_qs = sum_qs.extra(select={'chosen_date': 'CASE WHEN date_due IS NULL THEN date ELSE date_due END'}) else: sum_qs = sum_qs.extra(select={'chosen_date':'date'}) sum_qs = sum_qs.extra(selec…

Accepted Answer

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 to `extra(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 exist
extra_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.

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

Solution

Problem