42 Replies
Seen this doco page yet?
https://orm.drizzle.team/docs/crud#aggregations
Querying with SQL-like syntax [CRUD] – DrizzleORM
Drizzle ORM | %s
So i'm trying this:
And it's not throwing an error until I run it as get the following:
error rendering: PostgresError: column "comments.id" must appear in the GROUP BY clause or be used in an aggregate function
So I add a .groupBy(comments.id) to the end, and it works, but it's always just showing the count as 1.
It depends on what count should count.
If you need to count something based on a query for each row, you need a sub-query.
for example
Here, no need for groupBy because I want to count something for each row.
Having a little issue with getting the count for the child comments for the parentId I pass in.
For testing I have one root comment, which the first of these has two child comments, which the first of these has 3 child comments.
I am getting '2' for the numberOfComments field for both results when getting the 2nd level, but I want the field of the first of the two results to show 3 as I have three comments with the parentId set to that commentId.
Is there a way to get the current id for the comment it is currently on to use to compare like:
where (${comments.parentId} = ${id}
For each comment I want to show how many comments have their parentId set as that comments id.
The only passed in params to the function are:
postId: string, // used to ensure it's only comments for this post
parentId: string // get the comments that have this as their parentId
The only thing I think of is to sum 2 sub queries
The one you wrote and another where comments.parentId = comments.id
Will try to make a reproduction asap
I tried and failed 🥲. I can't resolve the recursivity involved in this query
Recursive CTEs?
This is an interesting one, I'll look into it later
Maybe from this schema
The SQL query should be:
I believe
Maybe last line could be
SELECT COUNT(*) FROM cte_comment;
Seems complex 😅 Do you know of how to convert this into drizzle format:
I've tried:
But getting errors. Argument of type 'string' is not assignable to parameter of type 'TemplateStringsArray'
And thanks everyone for trying to help on this!
I don't think that query is going to give you what you want.
We need to query the db recursively.
I don't have access to anything to test right now but you can try the following and see if that works:
Log the result and see what shape it has, this could be your starting point to tinker with it
Are you sure I need to do the count of child comments recursively? For each non root comment i'm doing an api call like:
/api/comments?postId=${postId}&parentId=${comment.id}
If the parentId is missing in the param it knows it's a root comment.
For getting all the initial root comments I do:
/api/comments?postId=${postId}&getRoot=${true}
So once all the child comments for a specific comment are retrieved, I just need to know the amount of child comments that each of them have.
In the api route i'm currently just doing one of either depending if the root is in the url param or not:
each do basically the same query as mentioned earlier in the thread, but the get root comments do:
while the non root query has:
I thought you wanted a count of all the child comments of a particular comment?
Yes that's what I need, for each comment to have a amountChildComments which shows how many comments live under that. Yeah I guess recursion is needed.
I'm just trying your execute now.
So nothing seems to be happening, 'starting' never shows unless I remove the execute:
Remember you need to change postId in the where clause for
${postId}
So quick update, it said column parentId does not exist. I assume because i'm using raw sql I should change it to the actual column name, not the ts version so I changed it to parent_id and that error went away. Now it's telling me: ' error rendering: PostgresError: relation "cte_comments" does not exist'