Another implementation supporting more general uses of CTE queries was
[https://groups.google.com/forum/#!topic/django-developers/b370mxfKCHg
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
[https://github.com/django/django/compare/master...ashleywaite:cte-dev 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
* version: 2.0 => master
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:1>
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:2>
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:3>
* cc: Matthew Pava (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:4>
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:5>
* cc: Tyson Clugg (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:6>
Comment (by pwfff):
Replying to [comment:5 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:7>
Comment (by pwfff):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:8>
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:9>
* cc: Matthew Schinckel (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:10>
* cc: Ole Laursen (added)
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:11>
Comment (by Daniel Miller):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:12>
* cc: Ian Foote (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:13>
* cc: Carlton Gibson (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:14>
* cc: TZanke (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:15>
* cc: Keryn Knight (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:16>
Comment (by Javier Buzzi):
Bump.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:17>
Comment (by Javier Buzzi):
Any updates?
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:18>
Comment (by felixxm):
Javier, patch is welcome. Leaving comments doesn't change anything and is
not helpful.
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:19>
* owner: nobody => Moses Mugisha
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:20>
* cc: Timothy Schilling (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:21>
* cc: Ryan Hiebert (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:22>
* cc: Markus Zapke-Gründemann (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:23>
* cc: Hannes Ljungberg (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:25>
* cc: Jameel A. (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:27>
* cc: Paolo Melchiorre (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:28>
* cc: Krzysztof Szularz (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:29>
* cc: crypted (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:30>
* cc: John Speno (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:31>
* cc: Julien Palard (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:32>
* cc: Dave Johansen (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:33>
* cc: Peter Lithammer (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:34>
* cc: Fabio Caritas Barrionuevo da Luz (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:35>
* cc: Sébastien Corbin (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:36>
* cc: Eron Lloyd (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:37>
* cc: Peter Thomassen (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:38>
* cc: Tom Carrick (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:39>
* cc: Dmytro Litvinov (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28919#comment:40>