Comments (3)
With(...)
constructs a common table expression to be placed in a SQLWITH
clause..join(...)
adds aJOIN
expression to the SQLFROM
clause, joining the CTE query to some other queryset or model..with_cte(...)
adds a CTE constructed withWith(...)
to aWITH
clause on the queryset.
Anatomy of the resulting SQL, slightly simplified for purposes of explanation.
WITH RECURSIVE "cte" AS ( -- WITH clause added by .with_cte(...)
-- CTE query constructed in With(...)
SELECT
"tests_order"."region_id",
SUM("tests_order"."amount") AS "total"
FROM "tests_order"
INNER JOIN "tests_region" ON (
"tests_order"."region_id" = "tests_region"."name"
)
WHERE "tests_region"."parent_id" = 'sun'
GROUP BY "tests_order"."region_id"
)
SELECT
"tests_order"."id",
"tests_order"."region_id",
"tests_order"."amount",
"cte"."total" AS "region_total"
FROM "tests_order"
INNER JOIN "cte" ON "tests_order"."region_id" = ("cte"."region_id") -- JOIN clause added by .join(...)
ORDER BY "tests_order"."amount" ASC
The above query (except for comments and basic formatting) was produced by adding print(orders.query)
to TestCTE.test_simple_cte_query
and then running nosetests tests/test_cte.py:TestCTE.test_simple_cte_query --nocapture
Does that make things more clear?
from django-cte.
Thanks, I worked that all out since and field a PR to simplify it a little by removing the need for .with_cte().
. And I'll PR a README improvement some time too if you're content with the default CTE PR.
Another minor hassle I found the hard way along the way using this (otherwise brilliant addition to Django landscape - so brilliant it should be absorbed into Django as it solves a huge problem with Django namely querying on an encapsulated queryset) is that the result of the .join() includes only the fields from its argument and not the fields from the CTE, which need explicitly to be added again with an annotation as and when needed. That took me a long time to work out, and SQL dumps.
I might try and tidy that up a bit too. We shall see.
from django-cte.
New documentation was added in #54. Please file new issues if there are gaps.
from django-cte.
Related Issues (20)
- is there a way to select from a cte without joining it with a model? HOT 5
- 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.