Opened 8 years ago

Closed 2 years ago

Last modified 2 years ago

#27412 closed New feature (fixed)

Coalesce function should work with subqueries

Reported by: Tzu-ping Chung Owned by: nobody
Component: Database layer (models, ORM) Version: 1.10
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tzu-ping Chung)

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.

Change History (5)

comment:1 by Tim Graham, 8 years ago

Triage Stage: UnreviewedAccepted

comment:2 by Tzu-ping Chung, 8 years ago

Description: modified (diff)
Summary: Coalesce function does not work with subqueriesCoalesce function should work with subqueries

comment:3 by Ekin Dursun, 6 years ago

Thanks to Subquery, I think this ticket can be closed.

comment:4 by Simon Charette, 2 years ago

Resolution: fixed
Status: newclosed

Confirmed support even without the Subquery wrapping in this PR.

comment:5 by Carlton Gibson <carlton@…>, 2 years ago

In b30c0081:

Refs #27412 -- Confirmed support for executing Coalesce(subquery).

This has been supported for subqueries wrapped in Subquery since the expression
was introduced and for Queryset directly since Subquery resolves to sql.Query.

Piggy-backed on the existing tests covering Coalesce handling of EmptyResultSet
as it seemed like a proper location to combine testing.

Note: See TracTickets for help on using tickets.
Back to Top