good way to get the "count" for paginated queries

I'm doing the following to get the total count and the values in a paginated query:

const fileCountRes = await ctx.db
  .select({
    count: sql<number>`count(*)`.mapWith(Number),
  })
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName));

const filesRes = await ctx.db
  .select()
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName))
  .limit(pageSize)
  .offset((currentPage - 1) * pageSize)
  .orderBy(desc(file.updatedAt));

const fileCountRes = await ctx.db
  .select({
    count: sql<number>`count(*)`.mapWith(Number),
  })
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName));

const filesRes = await ctx.db
  .select()
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName))
  .limit(pageSize)
  .offset((currentPage - 1) * pageSize)
  .orderBy(desc(file.updatedAt));

I wonder if there is a better way to do it. I'll add more things in the where to make a filter functionality and It would be great if I didn't have to add it in both places separately. Is there a way to do it (and still keep the type safety)? thank you in advance!

2 Replies

rphlmr ⚡•2y ago

Hello, Sorry for the 1st message, I misread the question at first. Maybe.

const where: SQL[] = [eq(bucket.name, bucketName)]  // your shared where conditions

const fileCountRes = await ctx.db
  .select({
    count: sql<number>`count(*)`.mapWith(Number),
  })
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(and(...where));

const filesRes = await ctx.db
  .select()
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(and(...where))
  .limit(pageSize)
  .offset((currentPage - 1) * pageSize)
  .orderBy(desc(file.updatedAt));

const where: SQL[] = [eq(bucket.name, bucketName)]  // your shared where conditions

const fileCountRes = await ctx.db
  .select({
    count: sql<number>`count(*)`.mapWith(Number),
  })
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(and(...where));

const filesRes = await ctx.db
  .select()
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(and(...where))
  .limit(pageSize)
  .offset((currentPage - 1) * pageSize)
  .orderBy(desc(file.updatedAt));

source: https://discord.com/channels/1043890932593987624/1109091751341342820/1109095820827037737

RaviOP•2y ago

Thanks @Raphaël Moreau I think this is a nice way to achieve what I wanted!

Gaming

Programming

good way to get the "count" for paginated queries