DT
Drizzle Team•15mo 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•15mo ago
cobite
cobiteOP•15mo 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 ⚡•15mo 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
cobiteOP•15mo 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 ⚡•15mo 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•15mo ago
Recursive CTEs? This is an interesting one, I'll look into it later
rphlmr ⚡
rphlmr ⚡•15mo 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•15mo 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
cobiteOP•15mo 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•15mo 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
cobiteOP•15mo 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•15mo ago
I thought you wanted a count of all the child comments of a particular comment?
cobite
cobiteOP•15mo 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•15mo ago
Remember you need to change postId in the where clause for ${postId}
cobite
cobiteOP•15mo 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'
Angelelz
Angelelz•15mo ago
The WITH clause says cte_comments and the select says cte_comment. Change either to match the other. Being that this is postgres, I might not have correct syntax as I know is Mysql You'll also need to change all column names for the sql column names
cobite
cobiteOP•15mo ago
same error: PostgresError: relation "cte_comments" does not exist' Do I need to create this relation somewhere? here is my relations in my post schema 
export const postsRelations = relations(posts, ({ many }) => ({
  comments: many(comments),
}));
export const postsRelations = relations(posts, ({ many }) => ({
  comments: many(comments),
}));
I changed to: WITH RECURSIVE cte_comments AS And seems to be getting something now, will update soon! So i'm getting the result as: Result(1) [ { count: '6' } ] I can get the count itself as: const count: number = Number(result[0].count); Is there a good way to add it to the select query? amountChildComments: count, Just gives me: 
Type 'number' is not assignable to type 'SQL<unknown> | Aliased<unknown> | PgColumn<ColumnBaseConfig<ColumnDataType, string>, {}, {}> | PgTable<TableConfig$1> | SelectedFieldsFlat<...>'
Type 'number' is not assignable to type 'SQL<unknown> | Aliased<unknown> | PgColumn<ColumnBaseConfig<ColumnDataType, string>, {}, {}> | PgTable<TableConfig$1> | SelectedFieldsFlat<...>'
And putting the sql directly like: 
 amountChildComments: sql<number>`WITH RECURSIVE cte_comments AS (
        SELECT       
            id,
            parent_id
        FROM       
            comments
        WHERE comments.post_id = ${postId} AND parent_id IS NULL
        UNION ALL
        SELECT 
            e.id, 
            e.parent_id
        FROM 
            comments e
            INNER JOIN cte_comments o 
                ON o.id = e.parent_id
    )
    SELECT COUNT(*) FROM cte_comments;`,
 amountChildComments: sql<number>`WITH RECURSIVE cte_comments AS (
        SELECT       
            id,
            parent_id
        FROM       
            comments
        WHERE comments.post_id = ${postId} AND parent_id IS NULL
        UNION ALL
        SELECT 
            e.id, 
            e.parent_id
        FROM 
            comments e
            INNER JOIN cte_comments o 
                ON o.id = e.parent_id
    )
    SELECT COUNT(*) FROM cte_comments;`,
I get: error rendering: PostgresError: syntax error at or near "WITH" Perhaps the drizzle sql does not support RECURSIVE?
Angelelz
Angelelz•15mo ago
Can you show me how you have it so far? Do you have the CTE in the select? I'm back home I might be able to get an example working But it looks like you almost got it Take a look at this video, it's really good:
Angelelz
Angelelz•15mo ago
https://planetscale.com/learn/courses/mysql-for-developers/queries/recursive-ctes?autoplay=1
Recursive CTEs
Learn about the full potential of a recursive SQL common table expression (CTE) in this video. Understand how to generate new data or work with existing data using this powerful SQL tool.
Angelelz
Angelelz•15mo ago
It's Mysql but it can't be too different from pastgres
cobite
cobiteOP•15mo ago
Hey! Yeah still at trying to get the command to fit into the select. The error is: error rendering: PostgresError: syntax error at or near "WITH" Not really sure what it might be, but assuming it's the 'RECURSION' even though it worked via an execute function as you provided. 
amountChildComments: sql<number>WITH RECURSIVE cte_comments AS (
        SELECT       
            id,
            parent_id
        FROM       
            comments
        WHERE comments.post_id = ${postId} AND parent_id IS NULL
        UNION ALL
        SELECT 
            e.id, 
            e.parent_id
        FROM 
            comments e
            INNER JOIN cte_comments o 
                ON o.id = e.parent_id
    )
    SELECT COUNT(*) FROM cte_comments;,
amountChildComments: sql<number>WITH RECURSIVE cte_comments AS (
        SELECT       
            id,
            parent_id
        FROM       
            comments
        WHERE comments.post_id = ${postId} AND parent_id IS NULL
        UNION ALL
        SELECT 
            e.id, 
            e.parent_id
        FROM 
            comments e
            INNER JOIN cte_comments o 
                ON o.id = e.parent_id
    )
    SELECT COUNT(*) FROM cte_comments;,
Thanks for the video link, will watch it now!
Angelelz
Angelelz•15mo ago
I don't think you'll be able fit it inside an outer select I didn't have time to look into it yesterday. I have the suspicion that the WITH statement could be done in a prepared statement
cobite
cobiteOP•15mo ago
The video helped a lot. I got the query working, just not getting the correct values yet. The issue was to remove the ; from the end of the statement, and to use the RECURSIVE word. This statement seems to query all comments that have the same postId and where they have no parentId. I also need to ensure they are children of each specific root comment 
  const countQuery = sql<number>`
     WITH RECURSIVE cte_comments AS (
       SELECT
           id,
           parent_id
       FROM
           comments
       WHERE comments.post_id = ${postId} AND parent_id IS NULL
       UNION ALL
       SELECT
           e.id,
           e.parent_id
       FROM
          comments e
           INNER JOIN cte_comments o
               ON o.id = e.parent_id
     )
     SELECT COUNT(*) FROM cte_comments
`;
  const countQuery = sql<number>`
     WITH RECURSIVE cte_comments AS (
       SELECT
           id,
           parent_id
       FROM
           comments
       WHERE comments.post_id = ${postId} AND parent_id IS NULL
       UNION ALL
       SELECT
           e.id,
           e.parent_id
       FROM
          comments e
           INNER JOIN cte_comments o
               ON o.id = e.parent_id
     )
     SELECT COUNT(*) FROM cte_comments
`;
Getting closer and should have it close to fixed tomorrow with a little code overhaul! my code to get all child comments is as following: 
const countQuery = sql<number>`
  WITH RECURSIVE cte_comments AS (
    SELECT
        id,
        parent_id
    FROM
        comments
    WHERE comments.post_id = ${postId} AND id = ${parentId} 
    UNION ALL
    SELECT
        e.id,
        e.parent_id
    FROM
       comments e
        INNER JOIN cte_comments o
            ON o.id = e.parent_id
  )
  SELECT COUNT(*) FROM cte_comments
`;
const countQuery = sql<number>`
  WITH RECURSIVE cte_comments AS (
    SELECT
        id,
        parent_id
    FROM
        comments
    WHERE comments.post_id = ${postId} AND id = ${parentId} 
    UNION ALL
    SELECT
        e.id,
        e.parent_id
    FROM
       comments e
        INNER JOIN cte_comments o
            ON o.id = e.parent_id
  )
  SELECT COUNT(*) FROM cte_comments
`;
I have total of 6 comments, one of which is a root comment (no parent id) The root comment shows the text as '6 more comments' (which should be 5) The first two nested comments of that root comment show '5 more comments', one of these show should 0 and the other should show 3. The 3 remaining nested comments all show '3 more comments'
Angelelz
Angelelz•15mo ago
This is an interesting problem. It's on the edge of my SQL knowledge. But my JS heart tells me that it's easier to get all the comments recursively and then do the aggregation in JS. Is your repo open source? I would like to do some testing but I don't want to set it up from scratch
cobite
cobiteOP•15mo ago
Hey thanks for all the assistance. I decided to just get all the child comments for the root comments, and then in js I just ordered the nested comments. It ended up saving a ton of api calls, and letting the users machine to deal with the computation. I've kind of encountered a brick wall again... wondering if you might know a fast solution. I'm currently just getting all the child comments for a post via: 
export default async function getAllChildComments(
  postId: string,
): Promise<Comment[]> {
  const results = await queryDB
    .select({
      id: comments.id,
      parentId: comments.parentId,
      username: users.username,
    })
    .from(comments)
    .leftJoin(users, eq(users.id, comments.userId))
    .where(and(isNotNull(comments.parentId), eq(comments.postId, postId)))
    .groupBy(comments.id, users.username);
  return results;
}
export default async function getAllChildComments(
  postId: string,
): Promise<Comment[]> {
  const results = await queryDB
    .select({
      id: comments.id,
      parentId: comments.parentId,
      username: users.username,
    })
    .from(comments)
    .leftJoin(users, eq(users.id, comments.userId))
    .where(and(isNotNull(comments.parentId), eq(comments.postId, postId)))
    .groupBy(comments.id, users.username);
  return results;
}
I'd like to instead get 3 levels of comments under a specific provided commentId? I think below would just get one level for example by adding eq(comments.parentId, commentId) : 
export default async function get3LevelsOfChildComments(
  postId: string,
  commentId: string
): Promise<Comment[]> {
  const results = await queryDB
    .select({
      id: comments.id,
      parentId: comments.parentId,
      username: users.username,
    })
    .from(comments)
    .leftJoin(users, eq(users.id, comments.userId))
    .where(and(isNotNull(comments.parentId), eq(comments.postId, postId), eq(comments.parentId, commentId)))
    .groupBy(comments.id, users.username);
  return results;
}
export default async function get3LevelsOfChildComments(
  postId: string,
  commentId: string
): Promise<Comment[]> {
  const results = await queryDB
    .select({
      id: comments.id,
      parentId: comments.parentId,
      username: users.username,
    })
    .from(comments)
    .leftJoin(users, eq(users.id, comments.userId))
    .where(and(isNotNull(comments.parentId), eq(comments.postId, postId), eq(comments.parentId, commentId)))
    .groupBy(comments.id, users.username);
  return results;
}
By getting 3 levels I can just add a load more comments button when it reaches the 3rd level in the UI and when clicked it can call get3LevelsOfChildComments and pass the current commentId, which will this load up to 3 more levels of comments.
Angelelz
Angelelz•15mo ago
I think there are 2 ways You could use a recursive CTE with an extra column for the depth, and just limit it to depth < 3 That is a complex query to create... But it's faster than the second option The second option is to just manually join 3 times You'll need to create 2 aliases for the comment table, and then join it back with itself 2 times
cobite
cobiteOP•15mo ago
I'm going to try the first approach, currently getting an error saying there is a syntax error near UNION: 
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
   SELECT
     c.id,
     c.parent_id,
     c.username,
     1 AS level
   FROM
     comments
   WHERE comments.post_id = ${postId} AND parent_id IS NOT NULL AND comments.parent_id = ${commentId}
   UNION ALL
   SELECT
     e.id,
     e.parent_id,
     e.username,
     c.level + 1 AS level
   FROM
     comments e
     INNER JOIN cte_comments c ON e.parent_id = c.id
   WHERE
     c.level < 3
 )
 SELECT
   id,
   parent_id,
   username
 FROM
   cte_comments
 ORDER BY
   level, id;
 `;

 const results: Comment[] = await queryDB.execute(sqlQuery);
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
   SELECT
     c.id,
     c.parent_id,
     c.username,
     1 AS level
   FROM
     comments
   WHERE comments.post_id = ${postId} AND parent_id IS NOT NULL AND comments.parent_id = ${commentId}
   UNION ALL
   SELECT
     e.id,
     e.parent_id,
     e.username,
     c.level + 1 AS level
   FROM
     comments e
     INNER JOIN cte_comments c ON e.parent_id = c.id
   WHERE
     c.level < 3
 )
 SELECT
   id,
   parent_id,
   username
 FROM
   cte_comments
 ORDER BY
   level, id;
 `;

 const results: Comment[] = await queryDB.execute(sqlQuery);
Angelelz
Angelelz•15mo ago
Are you missing a c in your first FROM comments?
cobite
cobiteOP•15mo ago
I don't think so, seems the same amount for all the selects from my side. Kinda confusing! 😅
Angelelz
Angelelz•15mo ago
Well, you're referring to an alias c all around your query but I don't see where you define it I just tested it, you have it! you were just missing that little c 
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
   SELECT
     c.id,
     c.parent_id,
     c.username,
     1 AS level
   FROM
     comments c
   WHERE comments.post_id = ${postId} AND parent_id IS NOT NULL AND comments.parent_id = ${commentId}
   UNION ALL
   SELECT
     e.id,
     e.parent_id,
     e.username,
     c.level + 1 AS level
   FROM
     comments e
     INNER JOIN cte_comments c ON e.parent_id = c.id
   WHERE
     c.level < 3
 )
 SELECT
   id,
   parent_id,
   username
 FROM
   cte_comments
 ORDER BY
   level, id;
 `;

 const results: Comment[] = await queryDB.execute(sqlQuery);
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
   SELECT
     c.id,
     c.parent_id,
     c.username,
     1 AS level
   FROM
     comments c
   WHERE comments.post_id = ${postId} AND parent_id IS NOT NULL AND comments.parent_id = ${commentId}
   UNION ALL
   SELECT
     e.id,
     e.parent_id,
     e.username,
     c.level + 1 AS level
   FROM
     comments e
     INNER JOIN cte_comments c ON e.parent_id = c.id
   WHERE
     c.level < 3
 )
 SELECT
   id,
   parent_id,
   username
 FROM
   cte_comments
 ORDER BY
   level, id;
 `;

 const results: Comment[] = await queryDB.execute(sqlQuery);
There are some performance optimizations you might be able to make later on. You could add a level column to the comments table to keep track of the indentation level in the actual data You could also add a childrenCount column to the comments, to keep track of it. This is a little more challenging to implement as you would need to update ALL parent comments whenever you insert a new child. But that would move the query complexity to the insert, which is a lot less common that the select, depending on your application
cobite
cobiteOP•15mo ago
Hey thanks for testing it! I added the c but i'm still getting: 
error rendering: PostgresError: syntax error at or near "UNION"
error rendering: PostgresError: syntax error at or near "UNION"
I've put a console.log before and after the execute, and it's just not getting past it... How would adding a level help with performance? I'm thinking to just load a user defined amount of levels (will hardcode to 3 now) and then when they get to the 3rd one the load more button will just refresh the component with the next level. The user can just click back to go back to the previous 3 levels. Ok so i've changed: 
WHERE comments.post_id = ${postId} AND parent_id IS NOT NULL AND comments.parent_id = ${commentId}
WHERE comments.post_id = ${postId} AND parent_id IS NOT NULL AND comments.parent_id = ${commentId}
to 
WHERE comments.post_id = ${postId} AND comments.parent_id IS NOT NULL AND comments.parent_id = ${commentId}
WHERE comments.post_id = ${postId} AND comments.parent_id IS NOT NULL AND comments.parent_id = ${commentId}
same error, now if I try: 
WHERE c.post_id = ${postId} AND c.parent_id IS NOT NULL AND c.parent_id = ${commentId}
WHERE c.post_id = ${postId} AND c.parent_id IS NOT NULL AND c.parent_id = ${commentId}
...same error If I make it just this temporarily: 
WHERE comments.post_id = ${postId}
WHERE comments.post_id = ${postId}
The error says: error rendering: PostgresError: syntax error at or near "FROM"
Angelelz
Angelelz•15mo ago
Interesting I see the problem. My testing was done in a Mysql db. I guess Mysql is more permissive Change this line INNER JOIN cte_comments c ON e.parent_id = c.id to INNER JOIN cte_comments ON e.parent_id = c.id That might not work. But I'm back to work. I could test it later, spin up some pg somewhere It's an interesting problem, I meant to say that using a recursive CTE might be more performant than 3 or 4 joins
cobite
cobiteOP•15mo ago
Thanks! So currently it's at: 
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
  SELECT
    c.id,
    c.parent_id,
    1 AS level
  FROM
    comments c
  WHERE c.post_id = ${postId} AND c.parent_id IS NOT NULL AND c.parent_id = ${commentId}
  UNION ALL
  SELECT
    e.id,
    e.parent_id,
    c.level + 1 AS level
  FROM
    comments e
    INNER JOIN cte_comments ON e.parent_id = c.id
  WHERE
    c.level < 3
)
SELECT
  id,
  parent_id,
FROM
  cte_comments
ORDER BY
  level, id;  
`;
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
  SELECT
    c.id,
    c.parent_id,
    1 AS level
  FROM
    comments c
  WHERE c.post_id = ${postId} AND c.parent_id IS NOT NULL AND c.parent_id = ${commentId}
  UNION ALL
  SELECT
    e.id,
    e.parent_id,
    c.level + 1 AS level
  FROM
    comments e
    INNER JOIN cte_comments ON e.parent_id = c.id
  WHERE
    c.level < 3
)
SELECT
  id,
  parent_id,
FROM
  cte_comments
ORDER BY
  level, id;  
`;
error: 
error rendering: PostgresError: syntax error at or near "FROM"
error rendering: PostgresError: syntax error at or near "FROM"
No pressure, i'll keep banging away at it until something breaks 🙂 If I remove the trailing , from here: 
  id,
  parent_id,
FROM
  id,
  parent_id,
FROM
I get: 
error rendering: PostgresError: missing FROM-clause entry for table "c"
error rendering: PostgresError: missing FROM-clause entry for table "c"
Had to just add the c to: 
INNER JOIN cte_comments c ON e.parent_id = c.id
INNER JOIN cte_comments c ON e.parent_id = c.id
Angelelz
Angelelz•15mo ago
Is it working now?
cobite
cobiteOP•15mo ago
Yes finally! Now I just need to add a join to use the comment.user_id to get the username from the users table. Not sure where to fit that in, but i'll get there eventually 🙂 I was previously doing it the other way: 
.leftJoin(users, eq(users.id, comments.userId))
.leftJoin(users, eq(users.id, comments.userId))
and 
   username: users.username,
   username: users.username,
Angelelz
Angelelz•15mo ago
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
  SELECT
    id,
    parent_id,
    user_id,
    1 AS level
  FROM
    comments
  WHERE post_id = ${postId} AND parent_id IS NOT NULL AND parent_id = ${commentId}
  UNION ALL
  SELECT
    c.id,
    c.parent_id,
    c.user_id
    level + 1
  FROM
    cte_comments e
    INNER JOIN comments c ON c.parent_id = e.id
  WHERE
    level < 3
)
SELECT
  id,
  parent_id,
  user_id,
  level
FROM
  cte_comments
INNER JOIN
  users on users.id = cte_comments.user_id
ORDER BY
  level, id;  
`;
const sqlQuery = sql`WITH RECURSIVE cte_comments AS (
  SELECT
    id,
    parent_id,
    user_id,
    1 AS level
  FROM
    comments
  WHERE post_id = ${postId} AND parent_id IS NOT NULL AND parent_id = ${commentId}
  UNION ALL
  SELECT
    c.id,
    c.parent_id,
    c.user_id
    level + 1
  FROM
    cte_comments e
    INNER JOIN comments c ON c.parent_id = e.id
  WHERE
    level < 3
)
SELECT
  id,
  parent_id,
  user_id,
  level
FROM
  cte_comments
INNER JOIN
  users on users.id = cte_comments.user_id
ORDER BY
  level, id;  
`;
That's from memory, you might need to tweak it a little bit Oh wait, that's plain query, To get drizzle types we might need to compose it better
cobite
cobiteOP•15mo ago
Oh ok, I was just adding username to the select and added your inner join, and the error was: error rendering: PostgresError: column reference "id" is ambiguous Get back to work though there's no rush with this. I'm learning a ton from you though so thanks! Think I have it working: 
  SELECT
    cte_comments.id,
    cte_comments.parent_id,
    users.username
  FROM
    cte_comments
  INNER JOIN
    users on users.id = cte_comments.user_id
  SELECT
    cte_comments.id,
    cte_comments.parent_id,
    users.username
  FROM
    cte_comments
  INNER JOIN
    users on users.id = cte_comments.user_id
Angelelz
Angelelz•15mo ago
That should give what you want Happy to help!
cobite
cobiteOP•15mo ago
The complexity creep is real.. 🥲 I'm trying to add a little feature that if it reaches the level 3 comments, it will check if they have children, and if they do then add a field called hasChildren. The reason for this is to be able to determine on the client side if a level 3 comment has children so that a load more button can be shown. Was trying something like: 
CASE WHEN EXISTS (
      SELECT 1
      FROM comments nc
      WHERE nc.parent_id = c.id
      AND c.level = 3
    ) THEN TRUE ELSE FALSE END AS has_children
CASE WHEN EXISTS (
      SELECT 1
      FROM comments nc
      WHERE nc.parent_id = c.id
      AND c.level = 3
    ) THEN TRUE ELSE FALSE END AS has_children
and below: 
    CASE WHEN EXISTS (
      SELECT 1
      FROM comments nc
      WHERE nc.parent_id = e.id
      AND c.level + 1 = 3
    ) THEN TRUE ELSE FALSE END AS has_children
    CASE WHEN EXISTS (
      SELECT 1
      FROM comments nc
      WHERE nc.parent_id = e.id
      AND c.level + 1 = 3
    ) THEN TRUE ELSE FALSE END AS has_children
No luck yet! I could just get max levels + 1, and then on the client side add the amount of children to level 3 objects and just do nothing with the level 4s, but I think it might be cleaner to just find out the amount via sql
Angelelz
Angelelz•15mo ago
Let me ask you a question, is this a personal project? That seems like a complicated query just to check if a comment have children. Honestly, there is no right or wrong on some of the architecture decisions you have to make. Everything is a trade off You only need to do select * from comments where parent_id = ${commentId} to see if a comment has children Now I suggested before to add metadata to each comment on insert... You could add a level column, as well as a has_children column to easily find this type of stuff Might as well add a children_count column just to have the number of direct children That would move some of the complexity to the insert statement instead of the select
cobite
cobiteOP•15mo ago
Hey, it's a project that me and a bunch of friends are working on. I managed to get it working with (based on your tip) : 
    (
      SELECT COUNT(*)
      FROM comments
      WHERE parent_id = cte_comments.id
    ) AS hasChildren
    (
      SELECT COUNT(*)
      FROM comments
      WHERE parent_id = cte_comments.id
    ) AS hasChildren
How would adding the metadata on insert work if I don't know how many replies a comment would get beforehand? You mean on each reply, update the parent ids hasChildren? I've seemed to have come full circle. So by just getting some of the comments back to the client, i'm only getting the amount of children within the data in the client, not the entire dataset. So I do actually need to get this at the server side 😅 I do have a level and an amountChildren (only gets direct children) so far, but I guess I need a totalDescendents too
Angelelz
Angelelz•15mo ago
Correct Total descendents seem kinda complicated and error prone, just because if somebody replies to a comment 10 levels deep, you'll have to update all of the parents all the way up In reddit, when you load comments, they just add a button called "load more" I bet they only have a column on the comments table called hasChildren or numberOfChildren And you'd add the "load more" button based on that column
Want results from more Discord servers?
Add your server