Drizzle TeamDT
Drizzle Team•2y ago
predaytor

Write SQL query in Drizzle?

Hey! How to write a similar query in drizzle? Basically, I need a way to count the total number of pages in a single query (should I?) for offset pagination?
const nodesCount = await context.db.select({ count: count() }).from(user).where(eq(user.role, 'student'));

const users = await context.db.query.user.findMany({
    limit: pageSize,
    offset: pageOffset,
    where: user => eq(user.role, 'student'),
    orderBy: (user, { asc }) => [asc(user.fullName), asc(user.id)],
});

vs. what I found on stackoveflow:
with cte as(
  select count(*) total from table 
)
select *, (select total from cte) total
from table limit 0, 100
Stack Overflow
How to count total number of rows when limit is added in android sq...
I am using SQLITE in android and I am having an issue I want to get the total count of row when limit is applied. For example if I apply limit of 100 for pagination but there are around 2000 data. ...
Was this page helpful?