Drizzle TeamDT
Drizzle Team•15mo ago
Anthony

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


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!
Was this page helpful?