DT
Drizzle Team•13mo 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");
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,
    },
});
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.
5 Replies
PWSey
PWSeyOP•13mo ago
The objects for reference with db.select: 
    id: 'id',
    feedbackNo: 53,
    userId: 'user-id',
    title: 'Title',
    body: { type: 'doc', content: [Array] },
    type: 'type',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:46:36.149Z,
    updatedAt: 2023-10-22T14:46:36.149Z,
    totalVotes: '1',
    votes: {
      id: 'id',
      userId: 'id',
      feedbackId: 'id',
      voteType: 'UP',
      createdAt: 2023-10-23T20:26:32.006Z,
      updatedAt: 2023-10-23T20:26:32.006Z
    },
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
    }
    id: 'id',
    feedbackNo: 53,
    userId: 'user-id',
    title: 'Title',
    body: { type: 'doc', content: [Array] },
    type: 'type',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:46:36.149Z,
    updatedAt: 2023-10-22T14:46:36.149Z,
    totalVotes: '1',
    votes: {
      id: 'id',
      userId: 'id',
      feedbackId: 'id',
      voteType: 'UP',
      createdAt: 2023-10-23T20:26:32.006Z,
      updatedAt: 2023-10-23T20:26:32.006Z
    },
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
    }
And this is with db.query: 
{
    id: 'id',
    feedbackNo: 51,
    userId: 'id',
    title: 'title',
    body: { type: 'doc', content: [Array] },
    type: 'general',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:41:12.460Z,
    updatedAt: 2023-10-22T14:41:12.460Z,
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
     
    },
    votes: [ [Object] ],
  },
{
    id: 'id',
    feedbackNo: 51,
    userId: 'id',
    title: 'title',
    body: { type: 'doc', content: [Array] },
    type: 'general',
    status: null,
    isPinned: false,
    createdAt: 2023-10-22T14:41:12.460Z,
    updatedAt: 2023-10-22T14:41:12.460Z,
    author: {
      id: 'id',
      name: 'name',
      email: 'email',
      emailVerified: null,
      image: 'image',
      role: 'USER',
      createdAt: 2023-09-29T00:01:31.189Z,
     
    },
    votes: [ [Object] ],
  },
Angelelz
Angelelz•13mo ago
You are trying to a lot in a single query I think you need a window function for the count, and a json aggregation for the votes It's doable but you have to check how to do it in sql and then translate to drizzle This is probably what you need for the count: https://discord.com/channels/1043890932593987624/1164627420368875529/1164662014237487299 And this is how somebody is using json aggregation: https://discord.com/channels/1043890932593987624/1165959913252585492/1165988316018118746
PWSey
PWSeyOP•13mo ago
  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:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(asc(feedback.id));
  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:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(asc(feedback.id));
I think I got it working, thanks the posts you have mentioned they were very helpful, I am not sure as you mentioned doing a lot in a single query I just did not want to do another trip to the database I believe it would be not ideal? I will be doing filtering but I want to do it in the database level as then when I implement pagination it will work properly. Thanks again! also how can I orderBy totalVotes when I order by feedbackId it seems to be working for some reason not sure, I was hoping I could orderBy totalVotes
Angelelz
Angelelz•13mo ago
You can pass a several parameters to orderBy I think you'll need an alias to be able to reference the totalVotes column
PWSey
PWSeyOP•13mo ago
I think I got it, thanks again! 
  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)`.as(
          "totalVotes"
        ),
      votes:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(desc(sql.identifier("totalVotes")));
  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)`.as(
          "totalVotes"
        ),
      votes:
        sql`json_agg(json_build_object('id', ${votes.id}, 'feedbackId', ${votes.feedbackId}, 'userId', ${votes.userId}, 'vote_type', ${votes.voteType}, 'createdAt', ${votes.createdAt}, 'updatedAt', ${votes.updatedAt}))`.as(
          "votes"
        ),
      users,
    })
    .from(feedback)
    .leftJoin(votes, eq(feedback.id, votes.feedbackId))
    .leftJoin(users, eq(feedback.userId, users.id))
    .groupBy(feedback.id, users.id)
    .orderBy(desc(sql.identifier("totalVotes")));
Want results from more Discord servers?
Add your server