id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27412	Coalesce function should work with subqueries	Tzu-ping Chung	nobody	"Say I have a model

{{{
class Foo(models.Model):
    val = models.IntegerField()
}}}


with data 1, 2, 3, 4, 5. In SQL I can do (roughly)

{{{
SELECT val FROM foo WHERE val >= COALESCE((SELECT val FROM foo WHERE val=6 LIMIT 1 OFFSET 0), 3);
}}}

to get 3, 4, and 5. Translated into Django ORM query language:

{{{
Foo.objects.filter(val__gte=Coalesce(Foo.objects.filter(val=6).values_list('val', flat=True)[:1], 3))
}}}

I get a ProgrammingError because Django does not handle subqueries, and passes the QuerySet object directly to the SQL backend.

The reason I don’t want to use first() instead is that I am doing some query-joining in a loop, and repeated first() calls result in lots of queries, dragging the database down."	New feature	closed	Database layer (models, ORM)	1.10	Normal	fixed			Accepted	0	0	0	0	0	0