Drizzle TeamDT
Drizzle Team•3y ago
PWSey

How to get a similar result of db.query but with extra aggregation and using select

I have the following drizzle query which works correctly but the results is not what I expect since we are grouping votes as well by their ids.
export const mostUpvoted = await db
  .select({
    id: feedback.id,
    feedbackNo: feedback.feedbackNo,
    userId: feedback.userId,
    title: feedback.title,
    body: feedback.body,
    type: feedback.type,
    status: feedback.status,
    isPinned: feedback.isPinned,
    createdAt: feedback.createdAt,
    updatedAt: feedback.updatedAt,
    totalVotes: sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`,
    votes: votes,
    author: users,
  })
  .from(feedback)
  .leftJoin(users, eq(feedback.userId, users.id))
  .leftJoin(votes, eq(votes.feedbackId, feedback.id))
  .groupBy(feedback.id, votes.id, users.id)
  .orderBy(asc(feedback.id))
  .prepare("most_upvoted");


I am trying to get
const test = await db.query.feedback.findMany({
    with: {
      author: true,
      votes: true,
    },
});

this results in all the votes which is what I am trying to achieve, but I am also trying to aggregate the total votes from the database as I believe it will be easier when filtering.
Was this page helpful?