Drizzle Team•6mo ago

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•6mo ago

guuyss, help pls

Sillvva•6mo 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•6mo ago

thanks! @Sillvva do you think this is more performant than the previous example? Because it counts for each user row. Thx!

Sillvva•6mo 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.

Gaming

Programming

Write SQL query in Drizzle?