DT
Drizzle Team•13mo ago
cobite

How to get the count of records? Here is my normal sql, trying to translate into drizzle

.select({
      id: comments.id,
      parentId: comments.parentId,
      // TODO countReplies: get the comment count where this id is listed as the parentId of other comments
      (
             SELECT COUNT(*)
             FROM comments AS childComments
             WHERE childComments.parentId = comments.id
           ) AS countReplies
    })
    .from(comments);
.select({
      id: comments.id,
      parentId: comments.parentId,
      // TODO countReplies: get the comment count where this id is listed as the parentId of other comments
      (
             SELECT COUNT(*)
             FROM comments AS childComments
             WHERE childComments.parentId = comments.id
           ) AS countReplies
    })
    .from(comments);
42 Replies
Oscar
Oscar•13mo ago
cobite
cobite•13mo ago
So i'm trying this: 
 countReplies: sql<number>`count(${eq(
        comments.parentId,
        parentId
      )})`,
 countReplies: sql<number>`count(${eq(
        comments.parentId,
        parentId
      )})`,
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.
rphlmr ⚡
rphlmr ⚡•13mo ago
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. 
    .select({
        id: User.id,
        ...
        numberOfComments:
            sql<number>`(select count(${comment.commentId}) from ${comment} where (${comment.authorId} = ${User.id}))`.as(
                "number_of_comments"
            ),
    .select({
        id: User.id,
        ...
        numberOfComments:
            sql<number>`(select count(${comment.commentId}) from ${comment} where (${comment.authorId} = ${User.id}))`.as(
                "number_of_comments"
            ),
for example Here, no need for groupBy because I want to count something for each row.
cobite
cobite•13mo ago
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. 
  .select({
      id: comments.id,
      amountChildComments: sql<number>`(select count(${comments.id}) from ${comments} where (${comments.parentId} = ${id <--- ?}))`,
    })
   .from(comments)
    .where(
      and(
        eq(comments.postId, postId),
        eq(comments.parentId, parentId)
      )
    )
  .select({
      id: comments.id,
      amountChildComments: sql<number>`(select count(${comments.id}) from ${comments} where (${comments.parentId} = ${id <--- ?}))`,
    })
   .from(comments)
    .where(
      and(
        eq(comments.postId, postId),
        eq(comments.parentId, parentId)
      )
    )
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
rphlmr ⚡
rphlmr ⚡•13mo ago
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
Angelelz
Angelelz•13mo ago
Recursive CTEs? This is an interesting one, I'll look into it later
rphlmr ⚡
rphlmr ⚡•13mo ago
export const comments = pgTable("comments", {
    id: uuid("id").primaryKey().defaultRandom(),
    text: text("text").notNull(),
    postId: integer("post_id").notNull().default(1),
    parentId: uuid("parent_id").references((): AnyPgColumn => comments.id),
});
export const comments = pgTable("comments", {
    id: uuid("id").primaryKey().defaultRandom(),
    text: text("text").notNull(),
    postId: integer("post_id").notNull().default(1),
    parentId: uuid("parent_id").references((): AnyPgColumn => comments.id),
});
Maybe from this schema
Angelelz
Angelelz•13mo ago
The SQL query should be: 
WITH cte_comments AS (
    SELECT       
        id,
        parentId
    FROM       
        comments
    WHERE comments.postId = postId AND parentId IS NULL
    UNION ALL
    SELECT 
        e.id, 
        e.parentId
    FROM 
        comments e
        INNER JOIN cte_comments o 
            ON o.id = e.parentId
)
SELECT * FROM cte_comment;
WITH cte_comments AS (
    SELECT       
        id,
        parentId
    FROM       
        comments
    WHERE comments.postId = postId AND parentId IS NULL
    UNION ALL
    SELECT 
        e.id, 
        e.parentId
    FROM 
        comments e
        INNER JOIN cte_comments o 
            ON o.id = e.parentId
)
SELECT * FROM cte_comment;
I believe Maybe last line could be SELECT COUNT(*) FROM cte_comment;
cobite
cobite•13mo ago
Seems complex 😅 Do you know of how to convert this into drizzle format: 
amountChildComments: sql<number>`(select count(${comments.id}) from ${comments} where (${comments.parentId} = ${id}))`,
amountChildComments: sql<number>`(select count(${comments.id}) from ${comments} where (${comments.parentId} = ${id}))`,
I've tried: 
   amountChildComments: sql<number>(`(select count(*) from ${comments} e inner join (select id from ${comments} where ${comments.postId} = ${postId} and ${comments.parentId} is null) o on o.id = e.parentId)`,
   amountChildComments: sql<number>(`(select count(*) from ${comments} e inner join (select id from ${comments} where ${comments.postId} = ${postId} and ${comments.parentId} is null) o on o.id = e.parentId)`,
But getting errors. Argument of type 'string' is not assignable to parameter of type 'TemplateStringsArray' And thanks everyone for trying to help on this!
Angelelz
Angelelz•13mo ago
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: 
db.execute(sql`WITH cte_comments AS (
    SELECT       
        id,
        parentId
    FROM       
        comments
    WHERE comments.postId = postId AND parentId IS NULL
    UNION ALL
    SELECT 
        e.id, 
        e.parentId
    FROM 
        comments e
        INNER JOIN cte_comments o 
            ON o.id = e.parentId
)
SELECT COUNT(*) FROM cte_comment;`);
db.execute(sql`WITH cte_comments AS (
    SELECT       
        id,
        parentId
    FROM       
        comments
    WHERE comments.postId = postId AND parentId IS NULL
    UNION ALL
    SELECT 
        e.id, 
        e.parentId
    FROM 
        comments e
        INNER JOIN cte_comments o 
            ON o.id = e.parentId
)
SELECT COUNT(*) FROM cte_comment;`);
Log the result and see what shape it has, this could be your starting point to tinker with it
cobite
cobite•13mo ago
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: 
 if (getRoot) {
    results = await getAllRootPostComments(postId);
  } else {
    results = await getAllPostChildComments(postId, parentId);
  }
 if (getRoot) {
    results = await getAllRootPostComments(postId);
  } else {
    results = await getAllPostChildComments(postId, parentId);
  }
each do basically the same query as mentioned earlier in the thread, but the get root comments do: 
 .where(and(isNull(comments.parentId), eq(comments.postId, postId)))
 .where(and(isNull(comments.parentId), eq(comments.postId, postId)))
while the non root query has: 
 .where(
      and(
        eq(comments.postId, postId),
        eq(comments.parentId, parentId)
      )
    )
 .where(
      and(
        eq(comments.postId, postId),
        eq(comments.parentId, parentId)
      )
    )
Angelelz
Angelelz•13mo ago
I thought you wanted a count of all the child comments of a particular comment?
cobite
cobite•13mo ago
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: 
let result = await queryDB.execute(sql`WITH cte_comments AS (
    SELECT       
        id,
        parentId
    FROM       
        comments
    WHERE comments.postId = postId AND parentId IS NULL
    UNION ALL
    SELECT 
        e.id, 
        e.parentId
    FROM 
        comments e
        INNER JOIN cte_comments o 
            ON o.id = e.parentId
)
SELECT COUNT(*) FROM cte_comment;`);
  console.log('starting');
  console.log(result);
let result = await queryDB.execute(sql`WITH cte_comments AS (
    SELECT       
        id,
        parentId
    FROM       
        comments
    WHERE comments.postId = postId AND parentId IS NULL
    UNION ALL
    SELECT 
        e.id, 
        e.parentId
    FROM 
        comments e
        INNER JOIN cte_comments o 
            ON o.id = e.parentId
)
SELECT COUNT(*) FROM cte_comment;`);
  console.log('starting');
  console.log(result);
Angelelz
Angelelz•13mo ago
Remember you need to change postId in the where clause for ${postId}
cobite
cobite•13mo ago
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'
Want results from more Discord servers?
Add your server