Comments (7)
I'm guessing a bit at what you're asking, but yes, that should be possible.
cte = With(
Order.objects
.values("region_id")
.annotate(number=Count("id"))
)
orders = (
cte.join(Order, region=cte.col.region_id)
.with_cte(cte)
.annotate(region_count=cte.col.number)
)
from django-cte.
Thank you. I assume this will work successfully.
I need to get the data for the other columns at the same time.
What if I need to include this number in the parent data to get a listing as shown below?
[
{
"id": 1,
"text": "...",
"reply_count": 10,
},
{
"id": 2,
"text": "...",
"reply_count": 7,
},
{
"id": 3,
"text": "...",
"reply_count": 3,
},
]
from django-cte.
You haven't listed the models (tables) you're working with or their relationships, and I'd prefer not to keep guessing. Can you provide the SQL query you want to produce? That will make it easier to tell if you can create the same query with django-cte.
from django-cte.
Sorry. The following are those.
To rearrange again, we need to get rows where the parent column is null, and
need to annotate the number of all its descendants
(all descendants of the hierarchy, not just the immediate children)
class CustomQuerySet(CTEQuerySet):
...
class Comment(models.Model):
post = models.ForeignKey(Post, on_delete=models.CASCADE)
text = models.TextField(max_length=1000)
parent = models.ForeignKey(
"self",
blank=True,
null=True,
on_delete=models.CASCADE,
related_name="replies",
)
objects = CustomQuerySet.as_manager()
I don't know the SQL statement that accomplishes what I want to do, but if I want to get the number of immediate children only
Here is what it would look like
Comment.objects.filter(post_id=post).annotate(reply_count=Count("replies"))
SELECT "posts_comment"."id",
"posts_comment"."text",
"posts_comment"."parent_id",
"posts_comment"."post_id",
COUNT(T3."id") AS "reply_count"
FROM "posts_comment"
LEFT OUTER JOIN "posts_comment" T3 ON ("posts_comment"."id" = T3."parent_id")
WHERE ("posts_comment"."post_id" = 465a22f4-12de-4a4e-a294-88f4abfdec5c
AND "posts_comment"."parent_id" IS NULL)
GROUP BY "posts_comment"."id"
from django-cte.
You can use a Recursive Common Table Expression to solve that problem. This sounds a bit like a homework problem, so I am stopping short of giving the answer.
from django-cte.
Of course, I know how to use the basic recursive common table expression, and I can get all descendants of the parent table, but I don't know how to annotate the number of all descendants (how to use a recursive common table expression in annotate?)
I don't know how to code the "reply_count=???"
Comment.objects.filter(parent__isnull=True).annotate(reply_count=???)
I know that the code to retrieve all descendants would look like this
def make_cte(cte):
return (
Comment.objects.filter(parent=parent)
.union(cte.join(Comment, parent_id=cte.col.id))
)
cte = With.recursive(make_cte)
return cte.queryset().with_cte(cte)
from django-cte.
I tried to annotate reply_count in this way, but reply_count is null for some reason.
def make_cte(cte):
return Comment.objects.filter(parent=OuterRef("pk")).union(
cte.join(Comment, parent_id=cte.col.id)
)
cte = With.recursive(make_cte)
subquery = Subquery(
cte.queryset()
.with_cte(cte)
.annotate(count=Count("pk"))
.values("count"),
output_field=IntegerField(),
)
Comment.objects.filter(post_id=post).annotate(reply_count=subquery)
from django-cte.
Related Issues (20)
- Improve documentation HOT 3
- 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
- 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.