Opened 5 weeks ago

Last modified 3 days ago

#28919 new New feature

Add support for Common Table Expression (CTE) queries

Reported by: Daniel Miller Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

django-cte-trees (also django-cte-forest) provides specialized uses of CTE queries for managing hierarchical data with recursive queries. To accomplish this, it uses the QuerySet.extra() API. This is my specific use case for CTE queries at the moment, however it leverages only one small part of what is possible with CTE queries: PostgreSQL, MySQL, SQL Server.

Another implementation supporting more general uses of CTE queries was presented on the developers mailing list, although I'm not sure it has ever made it any further than that. The code can be found on github. It appears to do its magic by mutating base_query.extra_tables, which seems to be a private/internal part of the ORM.

Change History (3)

comment:1 Changed 5 weeks ago by Tim Graham

Triage Stage: UnreviewedAccepted
Version: 2.0master

comment:2 Changed 5 weeks ago by Matthew Pava

I have the same issue. I've been thinking about this and wondering if maybe we could just convert the Subquery object into a CTE (common table expression). They ought to work just the same, and it really shouldn't break backwards compatibility. At the same time, we could add a kwarg to the Subquery object, recursive, so that we can create a recursive CTE. I think it would show how powerful the ORM is. It already handles everything else we need with RCTEs, including union queries and union all queries.

Last edited 5 weeks ago by Matthew Pava (previous) (diff)

comment:3 Changed 3 days ago by ErikW

I like Matthew's idea of reusing Subquery as the basis for CTEs since behind the scenes CTEs are really just syntactic sugar for Subqueries. I'd just like to add that being able to pull multiple values/aggregates from the Subquery/CTE would really push this over the top as a huge performance boost for complex analytical queries.

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