5 replies

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

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

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. ...

Drizzle Team•2y ago•

5 replies

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

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

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...

Write SQL query in Drizzle?

Similar Threads

Write SQL query in Drizzle?

Similar Threads

Similar Threads

Similar Threads