Opened 6 years ago

Last modified 8 weeks ago

#28919 assigned New feature

Add support for Common Table Expression (CTE) queries

Reported by: Daniel Miller Owned by: Moses Mugisha
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: QuerySet.extra
Cc: Matthew Pava, Tyson Clugg, Matthew Schinckel, Ole Laursen, Ian Foote, Carlton Gibson, TZanke, Keryn Knight, Timothy Schilling, Ryan Hiebert, Markus Zapke-Gründemann, Hannes Ljungberg, Krzysztof Jagiełło, Jameel A., Paolo Melchiorre, Krzysztof Szularz, Marko G., John Speno, Julien Palard, Dave Johansen, Peter Lithammer, Fabio Caritas Barrionuevo da Luz, Sébastien Corbin, Eron Lloyd, Peter Thomassen, Tom Carrick, Dmytro Litvinov 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 (40)

comment:1 by Tim Graham, 6 years ago

Triage Stage: UnreviewedAccepted
Version: 2.0master

comment:2 by Matthew Pava, 6 years ago

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 6 years ago by Matthew Pava (previous) (diff)

comment:3 by ErikW, 6 years ago

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 by Matthew Pava, 6 years ago

Cc: Matthew Pava added

comment:5 by Daniel Miller, 6 years ago

I implemented generic CTE support for Django: https://github.com/dimagi/django-cte

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

comment:6 by Tyson Clugg, 6 years ago

Cc: Tyson Clugg added

in reply to:  5 comment:7 by Aaron Lisman, 6 years ago

Replying to Daniel Miller:

I implemented generic CTE support for Django: https://github.com/dimagi/django-cte

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 by Aaron Lisman, 6 years ago

I have a PR here, but it's incomplete in some ways: https://github.com/dimagi/django-cte/pull/1
I could use some help from someone more familiar with the Query object and how it handles joins/aliases.

comment:9 by Daniel Miller, 6 years ago

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 by Matthew Schinckel, 6 years ago

Cc: Matthew Schinckel added

comment:11 by Ole Laursen, 6 years ago

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 by Daniel Miller, 6 years ago

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

Version 0, edited 6 years ago by Daniel Miller (next)

comment:13 by Ian Foote, 6 years ago

Cc: Ian Foote added

comment:14 by Carlton Gibson, 5 years ago

Cc: Carlton Gibson added

comment:15 by TZanke, 5 years ago

Cc: TZanke added

comment:16 by Keryn Knight, 5 years ago

Cc: Keryn Knight added

comment:17 by Javier Buzzi, 5 years ago

Bump.

comment:18 by Javier Buzzi, 5 years ago

Any updates?

comment:19 by Mariusz Felisiak, 5 years ago

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

comment:20 by Moses Mugisha, 4 years ago

Owner: changed from nobody to Moses Mugisha
Status: newassigned

comment:21 by Timothy Schilling, 4 years ago

Cc: Timothy Schilling added

comment:22 by Ryan Hiebert, 4 years ago

Cc: Ryan Hiebert added

comment:23 by Markus Zapke-Gründemann, 4 years ago

Cc: Markus Zapke-Gründemann added

comment:24 by Simon Charette, 4 years ago

I wonder if this isn't a feature we can combine with the request for adding QuerySet.alias (#27719).

Adding an alias to a QuerySet expression would result in a CTE and could then be easily pruned if the queryset drops all references to this alias.

Last edited 4 years ago by Simon Charette (previous) (diff)

comment:25 by Hannes Ljungberg, 4 years ago

Cc: Hannes Ljungberg added

comment:26 by Krzysztof Jagiełło, 4 years ago

Cc: Krzysztof Jagiełło added

comment:27 by Jameel A., 3 years ago

Cc: Jameel A. added

comment:28 by Paolo Melchiorre, 3 years ago

Cc: Paolo Melchiorre added

comment:29 by Krzysztof Szularz, 3 years ago

Cc: Krzysztof Szularz added

comment:30 by Marko G., 3 years ago

Cc: Marko G. added

comment:31 by John Speno, 2 years ago

Cc: John Speno added

comment:32 by Julien Palard, 2 years ago

Cc: Julien Palard added

comment:33 by Dave Johansen, 2 years ago

Cc: Dave Johansen added

comment:34 by Peter Lithammer, 2 years ago

Cc: Peter Lithammer added

comment:35 by Fabio Caritas Barrionuevo da Luz, 22 months ago

Cc: Fabio Caritas Barrionuevo da Luz added

comment:36 by Sébastien Corbin, 18 months ago

Cc: Sébastien Corbin added

comment:37 by Eron Lloyd, 16 months ago

Cc: Eron Lloyd added

comment:38 by Peter Thomassen, 4 months ago

Cc: Peter Thomassen added

comment:39 by Tom Carrick, 4 months ago

Cc: Tom Carrick added

comment:40 by Dmytro Litvinov, 8 weeks ago

Cc: Dmytro Litvinov added
Note: See TracTickets for help on using tickets.
Back to Top