DT
Drizzle Team•2mo 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,
});
};
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 âš¡
rphlmr ⚡•2mo ago
👋 not sure if it's what you want but we can do that: https://drizzle.run/jm1gydsbyvvu9nbq1q4y16t1
rphlmr âš¡
rphlmr ⚡•2mo 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 }));
Anthony
AnthonyOP•2mo 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 âš¡
rphlmr ⚡•2mo 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>;
Want results from more Discord servers?
Add your server