Drizzle pagination with count
Hi, is there a way to have a paginated query that also count all posible results based on a condition? I have the following currently:
const result = await db.query.businessUsers
.findMany({
orderBy: businessUsers.id,
limit: limit,
offset: offset,
where: or(
like(businessUsers.id, %${text}%),
like(businessUsers.name, %${text}%),
like(businessUsers.subdomain, %${text}%)
),
})
const count = await db
.select({ count: sql<number>count(*) })
.from(businessUsers)
.where(
or(
like(businessUsers.id, %${text}%),
like(businessUsers.name, %${text}%),
like(businessUsers.subdomain, %${text}%)
)
)
Is there a way to do this counting without having to repeat the whole query again?1 Reply
There isn’t a way in SQL, at least MySQL, to do that. It requires two queries
You may want to run both in a transaction if the consistency matters to you
And you’ll probably want to extract out the where query to a variable you can reuse to make sure it’s in sync