search

dimagi / django-cte

Common Table Expressions (CTE) for Django
Other
334 stars 46 forks source link

Support UNION queries that share a CTE #32

Open jalaziz opened 3 years ago

jalaziz commented 3 years ago

I would like to write queries of the form:

WITH cte AS (SELECT * FROM ....)
(SELECT * FROM cte WHERE ...
UNION
SELECT * FROM cte WHERE ...)

Unfortunately, it seems that when trying to build such a query, the WITH clause is no added to the query. I believe this is due to the first if clause in CTECompiler. generate_sql.

I'm happy to put up a PR to remove the clause and attempt to make it work, but curious if there's a more fundamental not adding the WITH clause to queries with combinations?

millerdev commented 3 years ago

I don't see a test that covers that scenario, and I can't remember if there is a workaround for queries with a combinator. That if clause should have probably raised NotImplementedError rather than returning as_sql(). But maybe that would have caused issues elsewhere?

A PR would be welcome. Please add tests as well.

gjones-r7 commented 3 years ago

Oh, I need this too! This is a handy library.