Opened 21 months ago

Last modified 33 hours 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: Matthew Pava, Tyson Clugg, Matthew Schinckel, Ole Laursen, Ian Foote, Carlton Gibson, TZanke, Keryn Knight Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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 (19)

comment:1 Changed 21 months ago by Tim Graham

Triage Stage: UnreviewedAccepted
Version: 2.0master

comment:2 Changed 20 months 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 20 months ago by Matthew Pava (previous) (diff)

comment:3 Changed 19 months 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.

comment:4 Changed 19 months ago by Matthew Pava

Cc: Matthew Pava added

comment:5 Changed 18 months ago by Daniel Miller

I implemented generic CTE support for Django:

Would be happy to contribute this to be included with Django. Feedback is welcome.

comment:6 Changed 18 months ago by Tyson Clugg

Cc: Tyson Clugg added

comment:7 in reply to:  5 Changed 17 months ago by Aaron Lisman

Replying to Daniel Miller:

I implemented generic CTE support for Django:

Would be happy to contribute this to be included with Django. Feedback is welcome.

This has worked well for me, with the one exception of not being able to do an outer join since the library just uses extra_tables.

comment:8 Changed 17 months ago by Aaron Lisman

I have a PR here, but it's incomplete in some ways:
I could use some help from someone more familiar with the Query object and how it handles joins/aliases.

comment:9 Changed 16 months ago by Daniel Miller

It's now been refactored to not use extra_tables, and there is a secret (undocumented, experimental API) way to do a left outer join.

comment:10 Changed 15 months ago by Matthew Schinckel

Cc: Matthew Schinckel added

comment:11 Changed 14 months ago by Ole Laursen

Cc: Ole Laursen added

Daniel Miller: Can I humbly suggest you raise this on django-developers? I would guess this needs a discussion of the actual API for adding the table expressions and joining with them.

comment:12 Changed 13 months ago by Daniel Miller

Ole Laursen: that's a good idea. Thank you for the suggestion. I'm a bit busy with other work right now, but I'll add it to my backlog.

Last edited 13 months ago by Daniel Miller (previous) (diff)

comment:13 Changed 12 months ago by Ian Foote

Cc: Ian Foote added

comment:14 Changed 6 months ago by Carlton Gibson

Cc: Carlton Gibson added

comment:15 Changed 4 months ago by TZanke

Cc: TZanke added

comment:16 Changed 2 months ago by Keryn Knight

Cc: Keryn Knight added

comment:17 Changed 2 months ago by Javier Buzzi


comment:18 Changed 44 hours ago by Javier Buzzi

Any updates?

comment:19 Changed 33 hours ago by felixxm

Javier, patch is welcome. Leaving comments doesn't change anything and is not helpful.

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