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

i'm trying use django's queryset api emulate following query:

select extract(year chosen_date) year,  extract(month chosen_date) month,  date_paid not null is_paid      (select (case when date_due null date_due else date end) chosen_date,* invoice_invoice) t1; 

the idea in situations, i'd rather use date_due column rather date column in situations, , since date_due optional, have use date fallback anyways, , create computed column chosen_date not have change rest of queries.

here first stab did @ emulating this, unable see how due null test base api went extra:

if(use_date_due):     sum_qs = sum_qs.extra(select={'chosen_date': 'case when date_due null date else date_due end'}) else:      sum_qs = sum_qs.extra(select={'chosen_date':'date'}) sum_qs = sum_qs.extra(select={'year': 'extract(year chosen_date)',                               'month': 'extract(month chosen_date)',                               'is_paid':'date_paid not null'}) 

but issue i'm having when run second query, error on how chosen_date column doesn't exist. i've had similar errors later on when trying use computed columns (like within annotate() calls), haven't found in documentation how computed columns differ "base" ones. have insight on this?

(edited python code because previous version had obvious logic flaw (forgot else branch). still doesn't work)

short answer: if create aliased (or computed) column using extra(select=...) cannot use aliased column in subsequent call filter(). also, you've discovered, can't use aliased column in later calls extra(select=...) or extra(where=...).

an attempt explain why:

for example:

qs = mymodel.objects.extra(select={'alias_col': 'title'})  #fielderror: cannot resolve keyword 'alias_col' field... filter_qs = qs.filter(alias_col='camembert')  #databaseerror: column "alias_col" not exist extra_qs = qs.extra(select={'another_alias': 'alias_col'}) 

filter_qs try produce query like:

select (title) "alias_col", "myapp_mymodel"."title" "myapp_mymodel" alias_col = "camembert"; 

and extra_qs tries like:

select (title) "alias_col", (alias_col) "another_alias",         "myapp_mymodel"."title" "myapp_mymodel"; 

neither of valid sql. in general, if want use computed column's alias multiple times in select or clauses of query need compute each time. why roman pekar's answer solves specific problem - instead of trying compute chosen_date once , use again later computes each time it's needed.

---

you mention annotation/aggregation in question. can use filter() on aliases created annotate() (so i'd interested in seeing similar errors you're talking about, it's been robust in experience). that's because when try filter on alias created annotate, orm recognizes you're doing , replaces alias computation created it.

so example:

qs = mymodel.objects.annotate(alias_col=max('id')) qs = qs.filter(alias_col__gt=0) 

produces like:

select "myapp_mymodel"."id", "myapp_mymodel"."title",         max("myapp_mymodel"."id") "alias_col" "myapp_mymodel" group "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 i've explained badly let me know , i'll see if can improve it.