Prisma•2y ago•

6 replies

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()

post.findMany()

to find posts for a specific user, but in this query I would like to include the number of

reactions

reactions

associated with a post for a kind of

reactionType

reactionType

. In my project, I am using emoji reactions on posts, and I need to return the number of reactions per emoji type (

reactionType

reactionType

). However, I am at a loss for how to do this without many queries per request.

Using

groupBy()

groupBy()

works if I grab the posts first and then use the

post.id

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

reactionType

... this would require additional queries.

I think the ideal structure would be an object called

reactionTypes

reactionTypes

of the following type appended to the

post

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

id

of the

reactionType

reactionType

like "

U+2764

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

reacted

boolean,

count

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

schema.prisma

(comment)

Solution

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

Jump to solution

Prisma•2y ago•

6 replies

.jsonp

Complicated groupBy query

post.findMany()

post.findMany()

to find posts for a specific user, but in this query I would like to include the number of

reactions

reactions

associated with a post for a kind of

reactionType

reactionType

. In my project, I am using emoji reactions on posts, and I need to return the number of reactions per emoji type (

reactionType

reactionType

). However, I am at a loss for how to do this without many queries per request.

Using

groupBy()

groupBy()

works if I grab the posts first and then use the

post.id

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' }
]

reactionType

reactionType

... this would require additional queries.

I think the ideal structure would be an object called

reactionTypes

reactionTypes

of the following type appended to the

post

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

id

of the

reactionType

reactionType

like "

U+2764

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

reacted

boolean,

count

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

schema.prisma

(comment)

Solution

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";

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

Jump to solution

Complicated groupBy query

Complicated groupBy query

Similar Threads

Similar Threads

Similar Threads