DT
Drizzle Team•8mo 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)],
});
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. ...
4 Replies
predaytor
predaytorOP•8mo ago
guuyss, help pls
Sillvva
Sillvva•8mo ago
import { count, getTableColumns } from 'drizzle-orm';

const cte = db.$with("cte").as(db.select({ total: count().as("total") }).from(table));

const result = await db
    .with(cte)
    .select({
        ...getTableColumns(table),
        total: sql`(SELECT ${cte.total} FROM ${cte})`.mapWith(Number).as("total")
    })
    .from(table)
    .limit(100);
import { count, getTableColumns } from 'drizzle-orm';

const cte = db.$with("cte").as(db.select({ total: count().as("total") }).from(table));

const result = await db
    .with(cte)
    .select({
        ...getTableColumns(table),
        total: sql`(SELECT ${cte.total} FROM ${cte})`.mapWith(Number).as("total")
    })
    .from(table)
    .limit(100);
https://orm.drizzle.team/docs/select#with-clause
Drizzle ORM - Select
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
predaytor
predaytorOP•8mo ago
thanks! @Sillvva do you think this is more performant than the previous example? Because it counts for each user row. Thx!
Sillvva
Sillvva•8mo ago
I'm not sure which is more performant, but the CTE is a temporary result set which is stored in memory. My guess is that it would be faster than making two separate round trips to the db.

Did you find this page helpful?