Comments (5)
Yes, you can do that. See the test_named_ctes
test.
Lines 252 to 255 in a3f35fe
Lines 267 to 270 in a3f35fe
Note that it is not necessary to use recursive CTEs, it's just something being done it that test.
You can run the test to see the generated SQL:
nosetests tests/test_cte.py:TestCTE.test_named_ctes --nocapture
For that one the query is
WITH RECURSIVE "region_paths" AS (
SELECT
"tests_region"."name",
"tests_region"."name" AS "path"
FROM
"tests_region"
WHERE
"tests_region"."parent_id" IS NULL
UNION
ALL
SELECT
"tests_region"."name",
COALESCE("region_paths"."path",) || COALESCE(
COALESCE(,) || COALESCE("tests_region"."name",),
) AS "path"
FROM
"tests_region"
INNER JOIN "region_paths" ON "tests_region"."parent_id" = ("region_paths"."name")
),
"region_groups" AS (
SELECT
"tests_region"."name",
"region_paths"."path" AS "parent_path",
"tests_region"."name" AS "parent_name"
FROM
"tests_region"
INNER JOIN "region_paths" ON "tests_region"."name" = ("region_paths"."name")
UNION
ALL
SELECT
"tests_region"."name",
"region_groups"."parent_path" AS "parent_path",
"region_groups"."parent_name" AS "parent_name"
FROM
"tests_region"
INNER JOIN "region_groups" ON "tests_region"."parent_id" = ("region_groups"."name")
),
"region_totals" AS (
SELECT
"region_groups"."parent_name" AS "name",
"region_groups"."parent_path" AS "path",
COUNT("tests_order"."id") AS "orders_count",
SUM("tests_order"."amount") AS "region_total"
FROM
"tests_order"
INNER JOIN "region_groups" ON "tests_order"."region_id" = ("region_groups"."name")
GROUP BY
"region_groups"."parent_name",
"region_groups"."parent_path"
)
SELECT
"tests_region"."name",
"tests_region"."parent_id",
"region_totals"."path" AS "path",
"region_totals"."orders_count" AS "orders_count",
"region_totals"."region_total" AS "region_total"
FROM
"tests_region"
INNER JOIN "region_totals" ON "tests_region"."name" = ("region_totals"."name")
ORDER BY
"path" ASC
from django-cte.
Sorry, I just noticed the second part of your question
after defining the CTEs, i want to select directly from C without having to join with a model. is that doable?
No, that is not possible, but why would you want to do that? Would it work to make the query in C be the final query instead of a CTE?
from django-cte.
No, that is not possible, but why would you want to do that? Would it work to make the query in C be the final query instead of a CTE?
I've currently run into such a thing.
I want to filter based on window function results and select * from cte_name;
.
To do that, I could annotate CTE with my window function, then select * from it with a where statement.
from django-cte.
No, that is not possible, but why would you want to do that? Would it work to make the query in C be the final query instead of a CTE?
I use the CTEs to get the data i need and then want to group/count the data.
I will explore your suggestion by using a raw_cte to count the data as D and then join with a model in the select.
Thank you for your help.
from django-cte.
Does this example help?
https://github.com/artemistomaras/django-cte-example
from django-cte.
Related Issues (20)
- Improve documentation HOT 3
- Usage in the context of time series HOT 1
- Combining with django-reversion HOT 2
- Can I count all children in Queryset annotate? HOT 7
- annotate() usage in recursion HOT 5
- Unneccessary outer join breaks query HOT 1
- With(empty_qs) throws Error HOT 1
- “no such column”/“column does not exist” errors in Django 4.2 HOT 9
- Not able to do full outer join with django_cte HOT 2
- hnej
- `.update()` does not work when accessing nested tables HOT 1
- Minor : typo in the doc
- How to use as default manager? HOT 1
- Prefetch objects with their nested children
- issue while trying to use with_cte on EmptyResultSet HOT 1
- issue while using cte with union querysets
- Django 4.2 error: each UNION query must have the same number of columns HOT 3
- it's wrong number when i wnat to get total number by using count()
- WITH query name specified more than once HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from django-cte.