Alaskan donut Comments - Answer Overflow

Alaskan donut

Explore posts from servers

•Created by Alaskan donut on 8/28/2024 in #help-and-questions

Complicated groupBy query

Hey Jon, your query worked well. I modified it return the structure I needed:

-- @param {String} $1:Post ID
-- @param {String} $2:User ID
SELECT
    p."id" AS "postId",
    reaction_types."id" AS "reactionTypeId",
    CAST(COUNT(reactions."id") AS INTEGER) AS "reactionCount",
    CASE
    WHEN COUNT(CASE WHEN reactions."user_id" = $2::uuid THEN 1 END) > 0 THEN TRUE
    ELSE FALSE
    END AS "reacted"
FROM posts p
LEFT JOIN reactions ON p."id" = reactions."post_id"
LEFT JOIN reaction_types ON reaction_types."id" = reactions."reaction_type_id"
WHERE p."id" = $1::uuid
GROUP BY p."id", p."user_id", reaction_types."id"
ORDER BY p."id", reaction_types."id";

-- @param {String} $1:Post ID
-- @param {String} $2:User ID
SELECT
    p."id" AS "postId",
    reaction_types."id" AS "reactionTypeId",
    CAST(COUNT(reactions."id") AS INTEGER) AS "reactionCount",
    CASE
    WHEN COUNT(CASE WHEN reactions."user_id" = $2::uuid THEN 1 END) > 0 THEN TRUE
    ELSE FALSE
    END AS "reacted"
FROM posts p
LEFT JOIN reactions ON p."id" = reactions."post_id"
LEFT JOIN reaction_types ON reaction_types."id" = reactions."reaction_type_id"
WHERE p."id" = $1::uuid
GROUP BY p."id", p."user_id", reaction_types."id"
ORDER BY p."id", reaction_types."id";

Here is the TypeScript that I used to merge the query into another posts query. This isn't procedure isn't necessary, since this could be handled with a single query, but I will deal with this later. Thanks again!

const result = await prisma.post.findMany({
  where: { user: { username: { equals: username, mode: "insensitive" } } },
  include: getPostInclude(user.id),
  orderBy: { createdAt: "desc" },
  take: pageSize + 1,
  cursor: cursor ? { id: cursor } : undefined,
});

const posts = result.slice(0, pageSize); // Posts are in desc order
const postIds = posts.map((post) => post.id);

const postTransactions = postIds.map((postId) => {
  return prisma.$queryRawTyped(getPostReactions(postId, user.id));
});

const postReactions = await prisma.$transaction(postTransactions); // Here is that query you provided! :)

const postsWithReactions = posts.map((post, index) => ({
  data: post,
  reactions: postReactions[index] as TPostReactions[],
}));

const nextCursor = posts.length > pageSize ? posts[pageSize].id : null;

const data: TProfilePostsPage = {
  posts: postsWithReactions,
  nextCursor,
};

const result = await prisma.post.findMany({
  where: { user: { username: { equals: username, mode: "insensitive" } } },
  include: getPostInclude(user.id),
  orderBy: { createdAt: "desc" },
  take: pageSize + 1,
  cursor: cursor ? { id: cursor } : undefined,
});

const posts = result.slice(0, pageSize); // Posts are in desc order
const postIds = posts.map((post) => post.id);

const postTransactions = postIds.map((postId) => {
  return prisma.$queryRawTyped(getPostReactions(postId, user.id));
});

const postReactions = await prisma.$transaction(postTransactions); // Here is that query you provided! :)

const postsWithReactions = posts.map((post, index) => ({
  data: post,
  reactions: postReactions[index] as TPostReactions[],
}));

const nextCursor = posts.length > pageSize ? posts[pageSize].id : null;

const data: TProfilePostsPage = {
  posts: postsWithReactions,
  nextCursor,
};

7 replies

PPrisma

•Created by Alaskan donut on 8/28/2024 in #help-and-questions

Complicated groupBy query

Thank you, @Jon Harrell! I greatly appreciate this.

7 replies

PPrisma

•Created by Alaskan donut on 8/28/2024 in #help-and-questions

Complicated groupBy query

model Post {
  id            String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  userId  String  @map("user_id") @db.Uuid
  mediaId String? @map("media_id") @db.Uuid

  user      User       @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  media     Media[]
  reactions Reaction[]

  @@map("posts")
}

model Reaction {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  postId         String @map("post_id") @db.Uuid
  reactionTypeId String @map("reaction_type_id")
  userId         String @map("user_id") @db.Uuid

  reactionType ReactionType @relation(fields: [reactionTypeId], references: [id])
  post         Post         @relation(fields: [postId], references: [id])
  user         User         @relation(fields: [userId], references: [id])

  @@unique([postId, userId, reactionTypeId])
  @@map("reactions")
}

model ReactionType {
  id    String @unique
  name  String @unique
  emoji String

  reaction Reaction[]

  @@map("reaction_types")
}

model Post {
  id            String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  userId  String  @map("user_id") @db.Uuid
  mediaId String? @map("media_id") @db.Uuid

  user      User       @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  media     Media[]
  reactions Reaction[]

  @@map("posts")
}

model Reaction {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  postId         String @map("post_id") @db.Uuid
  reactionTypeId String @map("reaction_type_id")
  userId         String @map("user_id") @db.Uuid

  reactionType ReactionType @relation(fields: [reactionTypeId], references: [id])
  post         Post         @relation(fields: [postId], references: [id])
  user         User         @relation(fields: [userId], references: [id])

  @@unique([postId, userId, reactionTypeId])
  @@map("reactions")
}

model ReactionType {
  id    String @unique
  name  String @unique
  emoji String

  reaction Reaction[]

  @@map("reaction_types")
}

7 replies

PPrisma

•Created by Alaskan donut on 6/6/2024 in #help-and-questions

Flat response from relational query?

If I have no choice but to manually flatten, I will just use a map function on the array.

4 replies

Gaming

Programming