Drizzle Team•7mo ago

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

// 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!

1 Reply

Jackson KasiOP•7mo ago

lapsed_users_ops.ts

// ** Import DB **
import { countDistinct, db, sql } from "@/db";
import { tbl_users, tbl_usage_metrics } from "@/db/schema";

// Function to get the count of Lapsed Users (users who haven't interacted in a significant period)
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)  // Ensuring that groupBy is used with having
    .having(sql`MAX(${tbl_usage_metrics.updatedAt}) <= ${lapsedThreshold} OR MAX(${tbl_usage_metrics.updatedAt}) IS NULL`);
}

// Function to get the details of Lapsed Users (first name, last name, email)
export async function getLapsedUserDetails() {
  const lapsedThreshold = Math.floor(
    (Date.now() - 30 * 24 * 60 * 60 * 1000) / 1000,
  ); // 30 days ago
  return db
    .selectDistinct({
      first_name: tbl_users.first_name,
      last_name: tbl_users.last_name,
      email: tbl_users.email,
    })
    .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`)
}

// ** Import DB **
import { countDistinct, db, sql } from "@/db";
import { tbl_users, tbl_usage_metrics } from "@/db/schema";

// Function to get the count of Lapsed Users (users who haven't interacted in a significant period)
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)  // Ensuring that groupBy is used with having
    .having(sql`MAX(${tbl_usage_metrics.updatedAt}) <= ${lapsedThreshold} OR MAX(${tbl_usage_metrics.updatedAt}) IS NULL`);
}

// Function to get the details of Lapsed Users (first name, last name, email)
export async function getLapsedUserDetails() {
  const lapsedThreshold = Math.floor(
    (Date.now() - 30 * 24 * 60 * 60 * 1000) / 1000,
  ); // 30 days ago
  return db
    .selectDistinct({
      first_name: tbl_users.first_name,
      last_name: tbl_users.last_name,
      email: tbl_users.email,
    })
    .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`)
}

Gaming

Programming

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