Drizzle Team•3mo ago

Struggling to build common pattern with queries -- fetching with count + offset/limit pagination

Hi team! Love what you all are doing with Drizzle. I have an extremely common pattern that I use in my application which is using Drizzle queries to create paginated results like so:

export const getFullSlots = async (options: GetFullSlotsOptions) => {
    const {
        ...
        page = 1,
        perPage = 20,
    } = options;

    return await db.query.slots.findMany({
        with: {
            campaign: true,
            site: true,
            study: true,
        },
        where: (slots, { and, gte, lte }) => (
               /* some filters */
            ),
        orderBy: (slots, { asc, sql }) => [
            sql`CASE WHEN ${slots.startTime} > CURRENT_TIMESTAMP THEN 0 ELSE 1 END`,
            asc(slots.startTime),
        ],
        limit: perPage,
        offset: Math.max(0, page - 1) * perPage,
    });
};

export const getFullSlots = async (options: GetFullSlotsOptions) => {
    const {
        ...
        page = 1,
        perPage = 20,
    } = options;

    return await db.query.slots.findMany({
        with: {
            campaign: true,
            site: true,
            study: true,
        },
        where: (slots, { and, gte, lte }) => (
               /* some filters */
            ),
        orderBy: (slots, { asc, sql }) => [
            sql`CASE WHEN ${slots.startTime} > CURRENT_TIMESTAMP THEN 0 ELSE 1 END`,
            asc(slots.startTime),
        ],
        limit: perPage,
        offset: Math.max(0, page - 1) * perPage,
    });
};

Now this works great for fetching rows -- the problem is for paginated results I want to also return the total count of rows, that the query returns. Not just the rows. I have managed to do this using the SQL API by creating a subquery, then fetching the count + the rows from that query and returning both. The problem is that when I use the query builder like above (which I prefer for complex nested objects) I don't know how to return the count or return the count before the limit and offset are applied. From what I've read in the docs, it seems like there is no way to do this with findMany and I have to refactor all my queries to use SQL syntax, am I correct? Thanks!

4 Replies

rphlmr ⚡•3mo ago

👋 not sure if it's what you want but we can do that: https://drizzle.run/jm1gydsbyvvu9nbq1q4y16t1

Drizzle Run

Paginated Query API with result and total count - Drizzle Run

rphlmr ⚡•3mo ago

const getUsers = async (
  page = 1,
  filter?: { pageSize?: number; startWith?: string },
) => {
  const pageSize = filter?.pageSize || 3;

  // We put here everything that filters the query: Where and With
  const baseConfig: FindManyQueryConfig<typeof db.query.users> = {
    where: (users, { like }) =>
      filter?.startWith
        ? like(users.firstName, `${filter.startWith}%`)
        : undefined,
    with: {
      profileInfo: true,
    },
    // add [with] too
  };

  const qCount = db.query.users.findMany({
    ...baseConfig,
    columns: { id: true },
  });

  // Run your paginated query and the total count query
  const [result, [{ totalCount }]] = await Promise.all([
    db.query.users.findMany({
      ...baseConfig,
      orderBy: (users, { asc }) => asc(users.id),
      limit: pageSize,
      offset: (page - 1) * pageSize,
    }),
    db.select({ totalCount: count() }).from(sql`${qCount}`),
  ]);

  return {
    data: result,
    resultCount: result.length,
    totalCount,
  };
};

console.log(await getUsers(1, { startWith: "E", pageSize: 2 }));

const getUsers = async (
  page = 1,
  filter?: { pageSize?: number; startWith?: string },
) => {
  const pageSize = filter?.pageSize || 3;

  // We put here everything that filters the query: Where and With
  const baseConfig: FindManyQueryConfig<typeof db.query.users> = {
    where: (users, { like }) =>
      filter?.startWith
        ? like(users.firstName, `${filter.startWith}%`)
        : undefined,
    with: {
      profileInfo: true,
    },
    // add [with] too
  };

  const qCount = db.query.users.findMany({
    ...baseConfig,
    columns: { id: true },
  });

  // Run your paginated query and the total count query
  const [result, [{ totalCount }]] = await Promise.all([
    db.query.users.findMany({
      ...baseConfig,
      orderBy: (users, { asc }) => asc(users.id),
      limit: pageSize,
      offset: (page - 1) * pageSize,
    }),
    db.select({ totalCount: count() }).from(sql`${qCount}`),
  ]);

  return {
    data: result,
    resultCount: result.length,
    totalCount,
  };
};

console.log(await getUsers(1, { startWith: "E", pageSize: 2 }));

AnthonyOP•3mo ago

@Raphaël M (@rphlmr) ⚡ this is perfect, you're amazing! @Raphaël M (@rphlmr) ⚡ hey thanks for the tip -- I'm having a problem using this with Typescript. When adding the with: clause to a call to findMany it automatically updates the Return type however the FindManyQueryConfig<typeof db.query.slots> is defined as a slot return type so the with: clause is ignored and thus the resulting type returned from the above getUsers function is not typed correctly. I see that it has to do with the <any, any> in

export type FindManyQueryConfig<T extends RelationalQueryBuilder<any, any>> =
    Parameters<T['findMany']>[0];

export type FindManyQueryConfig<T extends RelationalQueryBuilder<any, any>> =
    Parameters<T['findMany']>[0];

but I'm not sure how to get that type dynamically.

rphlmr ⚡•3mo ago

Hey 👋 thanks, I will fix it and report back! Oh, when you define with in the baseConfig. You can try

  const baseConfig = {
    where: (users, { like }) =>
      filter?.startWith
        ? like(users.firstName, `${filter.startWith}%`)
        : undefined,
    with: { profileInfo: true },
  } satisfies FindManyQueryConfig<typeof db.query.users>;

  const baseConfig = {
    where: (users, { like }) =>
      filter?.startWith
        ? like(users.firstName, `${filter.startWith}%`)
        : undefined,
    with: { profileInfo: true },
  } satisfies FindManyQueryConfig<typeof db.query.users>;

Gaming

Programming

Struggling to build common pattern with queries -- fetching with count + offset/limit pagination