MoltenFuzzy
MoltenFuzzy
Explore posts from servers
CC#TTCTheo's Typesafe Cult
PostsComments
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 5/31/2023 in #questions
N Nested Relations
updated version includes user 
      const result = await ctx.prisma.$queryRaw<Comment[]>`
          WITH RECURSIVE comment_replies AS (
              SELECT c.id, c.content, c."parentCommentId", c."authorId"
              FROM "Comment" c
              JOIN "User" u ON u.id = c."authorId"
              WHERE c."parentCommentId" IS NULL
              AND c."postId" = ${input.postId}

              UNION ALL

              SELECT c.id, c.content, c."parentCommentId", c."authorId"
              FROM "Comment" c
              INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
          )
          SELECT cr.id, cr.content, cr."parentCommentId", cr."authorId", u.username, u.image
          FROM comment_replies cr
          JOIN "User" u ON u.id = cr."authorId";`;
      const result = await ctx.prisma.$queryRaw<Comment[]>`
          WITH RECURSIVE comment_replies AS (
              SELECT c.id, c.content, c."parentCommentId", c."authorId"
              FROM "Comment" c
              JOIN "User" u ON u.id = c."authorId"
              WHERE c."parentCommentId" IS NULL
              AND c."postId" = ${input.postId}

              UNION ALL

              SELECT c.id, c.content, c."parentCommentId", c."authorId"
              FROM "Comment" c
              INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
          )
          SELECT cr.id, cr.content, cr."parentCommentId", cr."authorId", u.username, u.image
          FROM comment_replies cr
          JOIN "User" u ON u.id = cr."authorId";`;
9 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 5/31/2023 in #questions
N Nested Relations
model Comment {
    id              String    @id @default(cuid())
    content         String    @db.VarChar(255)
    published       Boolean   @default(false)
    authorId        String
    author          User      @relation(fields: [authorId], references: [id])
    postId          String
    post            Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
    likesCount      Int       @default(0)
    likedBy         User[]    @relation("likedComments")
    dislikesCount   Int       @default(0)
    dislikedBy      User[]    @relation("dislikedComments")
    repliesCount    Int       @default(0)
    replies         Comment[] @relation("replies")
    parentCommentId String?
    parentComment   Comment?  @relation("replies", fields: [parentCommentId], references: [id])
    createdAt       DateTime  @default(now())
    updatedAt       DateTime  @updatedAt
}
model Comment {
    id              String    @id @default(cuid())
    content         String    @db.VarChar(255)
    published       Boolean   @default(false)
    authorId        String
    author          User      @relation(fields: [authorId], references: [id])
    postId          String
    post            Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
    likesCount      Int       @default(0)
    likedBy         User[]    @relation("likedComments")
    dislikesCount   Int       @default(0)
    dislikedBy      User[]    @relation("dislikedComments")
    repliesCount    Int       @default(0)
    replies         Comment[] @relation("replies")
    parentCommentId String?
    parentComment   Comment?  @relation("replies", fields: [parentCommentId], references: [id])
    createdAt       DateTime  @default(now())
    updatedAt       DateTime  @updatedAt
}
9 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 5/31/2023 in #questions
N Nested Relations
sure
9 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 5/31/2023 in #questions
N Nested Relations
the result is this 
data: [
  {
      id: 'clifwdlup0016hpuohylng1ie',
      content: '1',
      parentCommentId: null,
    replies: [
      {
          id: 'clifwdmxt0017hpuo3em7atry',
          content: '2',
          parentCommentId: 'clifwdlup0016hpuohylng1ie',
          replies: []
        }
      ]
    }, { id: 'clifwfl750018hpuoib1191m0', content: 'he', parentCommentId: null, replies: [] },
    
  ]
data: [
  {
      id: 'clifwdlup0016hpuohylng1ie',
      content: '1',
      parentCommentId: null,
    replies: [
      {
          id: 'clifwdmxt0017hpuo3em7atry',
          content: '2',
          parentCommentId: 'clifwdlup0016hpuohylng1ie',
          replies: []
        }
      ]
    }, { id: 'clifwfl750018hpuoib1191m0', content: 'he', parentCommentId: null, replies: [] },
    
  ]
9 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 5/31/2023 in #questions
N Nested Relations
Im not too sure how to have the sql query structure in that way so i just used a recursive CTE on my Comment table and wrote a recursive function to process the the result into the the structure of my interface. 
getAllFromPost: publicProcedure
    .input(z.object({ postId: z.string() }))
    .query(async ({ ctx, input }) => {
      /* 
      instead go directly to comment table and search for comments with the postId
      we know comments with null parentCommentIds are Top level comments
      take those and recurse through them to until we reach comments with no replies in their list?
      PROBLEM IS I CANT GET THE REPLIES LIST SINCE IT IS A RELATIONSHIP
      */
      interface Comment {
        id: string;
        content: string;
        parentCommentId: string | null;
        replies?: Comment[];
      }

      const result = await ctx.prisma.$queryRaw<Comment[]>`
          WITH RECURSIVE comment_replies AS (
            SELECT id, content, "parentCommentId"
            FROM "Comment"
            WHERE "Comment"."parentCommentId" IS NULL
            AND "Comment"."postId" = ${input.postId}

            UNION ALL

            SELECT c.id, c.content, c."parentCommentId"
            FROM "Comment" c
            INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
          )
          SELECT * FROM comment_replies;`;

      // Helper function to recursively build the comment tree
      function buildCommentTree(
        comments: Comment[],
        parent_id: string | null = null
      ): Comment[] {
        const commentTree: Comment[] = [];
        for (const comment of comments) {
          if (comment.parentCommentId === parent_id) {
            const childComments = buildCommentTree(comments, comment.id);
            if (childComments.length > 0) {
              comment.replies = childComments;
            } else {
              comment.replies = [];
            }
            commentTree.push(comment);
          }
        }
        return commentTree;
      }

      return buildCommentTree(result);
    }),
getAllFromPost: publicProcedure
    .input(z.object({ postId: z.string() }))
    .query(async ({ ctx, input }) => {
      /* 
      instead go directly to comment table and search for comments with the postId
      we know comments with null parentCommentIds are Top level comments
      take those and recurse through them to until we reach comments with no replies in their list?
      PROBLEM IS I CANT GET THE REPLIES LIST SINCE IT IS A RELATIONSHIP
      */
      interface Comment {
        id: string;
        content: string;
        parentCommentId: string | null;
        replies?: Comment[];
      }

      const result = await ctx.prisma.$queryRaw<Comment[]>`
          WITH RECURSIVE comment_replies AS (
            SELECT id, content, "parentCommentId"
            FROM "Comment"
            WHERE "Comment"."parentCommentId" IS NULL
            AND "Comment"."postId" = ${input.postId}

            UNION ALL

            SELECT c.id, c.content, c."parentCommentId"
            FROM "Comment" c
            INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
          )
          SELECT * FROM comment_replies;`;

      // Helper function to recursively build the comment tree
      function buildCommentTree(
        comments: Comment[],
        parent_id: string | null = null
      ): Comment[] {
        const commentTree: Comment[] = [];
        for (const comment of comments) {
          if (comment.parentCommentId === parent_id) {
            const childComments = buildCommentTree(comments, comment.id);
            if (childComments.length > 0) {
              comment.replies = childComments;
            } else {
              comment.replies = [];
            }
            commentTree.push(comment);
          }
        }
        return commentTree;
      }

      return buildCommentTree(result);
    }),
9 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 5/31/2023 in #questions
N Nested Relations
Problem resolved, will post my solution later
9 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
^ yup
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
bet tysm
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
thank you
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
interesting
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
ah
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
hmm it still is giving me an error since the types dont match up with the props types, which i can union an undefined to fix that
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
this isnt really a big problem but adding checks to all my props isnt very pretty
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
i added a check for errors, but the type | undefined is still there
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
  const {
    data: postData,
    isError,
    error,
  } = api.post.getOne.useQuery({ id: postId }, { enabled: true });

  const { data: postComments } = api.comment.getAllFromPost.useQuery(
    { postId },
    { enabled: true }
  );

  if (isError) {
    return <span>Error: {error.message}</span>;
  }
  const {
    data: postData,
    isError,
    error,
  } = api.post.getOne.useQuery({ id: postId }, { enabled: true });

  const { data: postComments } = api.comment.getAllFromPost.useQuery(
    { postId },
    { enabled: true }
  );

  if (isError) {
    return <span>Error: {error.message}</span>;
  }
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
as far as i know
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
40 replies
TTCTheo's Typesafe Cult
Created by MoltenFuzzy on 3/6/2023 in #questions
| undefined on tRPC procedures
  getOne: publicProcedure
    .input(z.object({ id: z.string() }))
    .query(async ({ ctx, input }) => {
      const post = await ctx.prisma.post.findUniqueOrThrow({
        where: { id: input.id },
        // joins the tables
        include: {
          author: true,
          images: true,
          // likedBy: true,
          // dislikedBy: true,
        },
      });

      await embedPostImageUrls([post as FullPost]);
      return post;
    }),
  getOne: publicProcedure
    .input(z.object({ id: z.string() }))
    .query(async ({ ctx, input }) => {
      const post = await ctx.prisma.post.findUniqueOrThrow({
        where: { id: input.id },
        // joins the tables
        include: {
          author: true,
          images: true,
          // likedBy: true,
          // dislikedBy: true,
        },
      });

      await embedPostImageUrls([post as FullPost]);
      return post;
    }),
40 replies