Drizzle TeamDT
Drizzle Team•2y ago
Jackson Kasi

Handling Aggregated Counts with GroupBy in Drizzle ORM: Requesting Community Insights

Hello Drizzle ORM Community,

I'm currently working on a TypeScript project where I use Drizzle ORM to manage user and usage metrics data. I have a function, getLapsedUserCount, designed to return the count of users who haven't interacted with our application within the last 30 days. However, I'm facing an issue with the aggregation of counts. Instead of getting a single total count, the function is returning individual counts for each group, which isn't what I need.

Here’s the relevant part of my code:
// Function to get the count of Lapsed Users
export async function getLapsedUserCount() {
  const lapsedThreshold = Math.floor(
    (Date.now() - 30 * 24 * 60 * 60 * 1000) / 1000,
  ); // 30 days ago
  return db
    .select({ count: countDistinct(tbl_users.external_id) })
    .from(tbl_users)
    .leftJoin(
      tbl_usage_metrics,
      sql`${tbl_users.external_id} = ${tbl_usage_metrics.external_id}`,
    )
    .groupBy(tbl_users.external_id)
    .having(sql`MAX(${tbl_usage_metrics.updatedAt}) <= ${lapsedThreshold} OR MAX(${tbl_usage_metrics.updatedAt}) IS NULL`);
}

This function is supposed to return a total count of lapsed users, but instead, it gives multiple records, each with a count of 1. I expect to receive a single count value, like {"count": [{"count": 53}]}.

The getLapsedUserDetails function works perfectly, fetching detailed lists of lapsed users correctly using a similar query structure.

Can anyone help me understand why the getLapsedUserCount function is not aggregating the counts into a single total and how I might correct this? Any advice or insights would be greatly appreciated!

Thank you!
image.png
image.png
Was this page helpful?