1 reply

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

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`);
}

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

{"count": [{"count": 53}]}

.

The

getLapsedUserDetails

getLapsedUserDetails

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

Can anyone help me understand why the

getLapsedUserCount

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!

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

getLapsedUserCount

// 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`);
}

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

{"count": [{"count": 53}]}

.

The

getLapsedUserDetails

getLapsedUserDetails

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

Can anyone help me understand why the

getLapsedUserCount

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!

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

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

Similar Threads

Similar Threads

Similar Threads