Prisma•3w ago

Complicated groupBy query

Hey there, I am attempting to do something with Prisma that may be undoable. I am still very new to web dev and Prisma, so be aware I don't doubt I'm missing something obvious here. I would like to use post.findMany() to find posts for a specific user, but in this query I would like to include the number of reactions associated with a post for a kind of reactionType. In my project, I am using emoji reactions on posts, and I need to return the number of reactions per emoji type (reactionType). However, I am at a loss for how to do this without many queries per request. Using groupBy() works if I grab the posts first and then use the post.id of each post...

prisma.reaction.groupBy({ 
  by: "reactionTypeId",
  where: { postId: { in: postList } },
  _count: true,
})

prisma.reaction.groupBy({ 
  by: "reactionTypeId",
  where: { postId: { in: postList } },
  _count: true,
})

^ This returns the following:

[
  { _count: 1, reactionTypeId: 'U+1FAE0' },
  { _count: 2, reactionTypeId: 'U+2764' }
]

[
  { _count: 1, reactionTypeId: 'U+1FAE0' },
  { _count: 2, reactionTypeId: 'U+2764' }
]

...and then merging the results from this with the original posts list, but this method is fairly complicated and I still haven't solved how to include a boolean value which indicates whether or not the current user has reacted to this post with a given emoji/reactionType... this would require additional queries. I think the ideal structure would be an object called reactionTypes of the following type appended to the post object:

type ReactionType = {
  [key: string]: {
    id: string,
    reacted: boolean
  }
}

type ReactionType = {
  [key: string]: {
    id: string,
    reacted: boolean
  }
}

... where the key here is the id of the reactionType like "U+2764" which is the unicode for a red heart emoji. Of course, I don't exactly need this format, I just need this data 🙂 (i.e., reacted boolean, count number) If anyone has thought about how I might be best include this reaction data, I would greatly appreciate it. I've been struggling for hours on this. For schema-related details, see the below schema.prisma (comment)

Solution:

Hey Jon, your query worked well. I modified it return the structure I needed: ```sql -- @param {String} $1:Post ID -- @param {String} $2:User ID...

Jump to solution

5 Replies

Alaskan donut•3w ago

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")
}

jonfanz•3w ago

Hacked around a bit and I think this is close to what you want:

SELECT
  p.id,
  p."userId",
  r."reactionTypeId",
  COUNT(r.id) as reaction_count
FROM
  "Post" p
LEFT JOIN
  "Reaction" r ON p.id = r."postId"
WHERE
  p."userId" = $1
GROUP BY
  p.id, p."userId", r."reactionTypeId"
ORDER BY
  p.id, r."reactionTypeId"

SELECT
  p.id,
  p."userId",
  r."reactionTypeId",
  COUNT(r.id) as reaction_count
FROM
  "Post" p
LEFT JOIN
  "Reaction" r ON p.id = r."postId"
WHERE
  p."userId" = $1
GROUP BY
  p.id, p."userId", r."reactionTypeId"
ORDER BY
  p.id, r."reactionTypeId"

Obviously I don't have your data set so I can't confirm if that would be exactly what you want.

Alaskan donut•3w ago

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

jonfanz•3w ago

let me know how it works for you. I can try and tweak things to see if I can get a better answer, but I think that will help get you in the right direction.

Solution

Alaskan donut•3w ago

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,
};

Gaming

Programming

Complicated groupBy query