Drizzle Team•3mo ago

How to get paginated result with the total count?

I want to show pagination in my frontend and calculate the total pages number. I am able to do the paginated query but I also want to select total of the filtered query without pagination. Is this possible within one query? Or do I need to do 2 queries? I haven't found the way of doing this, I was trying to somehow do this using "with" clause (https://orm.drizzle.team/docs/select#with-clause) but I am not sure if I get it right. The idea was to do this:

const filters = []; // some filters here

const total = db.$with("total").as(
      db
        .select({ count: count(tab.id) })
        .from(tab)
        .where(and(...filters))
    );

const result = await withPagination(db
        .with(total)
        .select({
          tab: getTableColumns(tab),
          tab1: getTableColumns(tab1),
          tab2: getTableColumns(tab2),
          total: total, // not entirely sure what to do here or if even I understand it correctly
        })
        .from(tab)
        .leftJoin(tab1, eq(tab.id, tab1.tabId))
        .leftJoin(tab2, eq(tab.id, tab2.tabId))
        .where(and(...filters))
        .$dynamic()
   );

function withPagination<T extends SQLiteSelect>(
  props: {
    query: T;
    orderBy: SQL[];
  } & Page
) {
  return props.query
    .orderBy(...props.orderBy)
    .limit(props.pageSize)
    .offset((props.page - 1) * props.pageSize);
}

const filters = []; // some filters here

const total = db.$with("total").as(
      db
        .select({ count: count(tab.id) })
        .from(tab)
        .where(and(...filters))
    );

const result = await withPagination(db
        .with(total)
        .select({
          tab: getTableColumns(tab),
          tab1: getTableColumns(tab1),
          tab2: getTableColumns(tab2),
          total: total, // not entirely sure what to do here or if even I understand it correctly
        })
        .from(tab)
        .leftJoin(tab1, eq(tab.id, tab1.tabId))
        .leftJoin(tab2, eq(tab.id, tab2.tabId))
        .where(and(...filters))
        .$dynamic()
   );

function withPagination<T extends SQLiteSelect>(
  props: {
    query: T;
    orderBy: SQL[];
  } & Page
) {
  return props.query
    .orderBy(...props.orderBy)
    .limit(props.pageSize)
    .offset((props.page - 1) * props.pageSize);
}

Is this approach even possible? Or do I need to do 2 separate queries? Thanks in advance for the help - if there is more information needed for this, please let me know.

Drizzle ORM - Select

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

4 Replies

The Safe Fire!•3mo ago

for me, i just use batch queries as its not too bad having 2 queries, and i dont have the extra latency this way (you also possibly could so some chaos selecting and then query but i dont think its worth it)

ivan•3mo ago

Hi @RiskyMH can you provide an example please?

ShawnMclean•3mo ago

Documenso who used prisma wraps this into 2 queries with a promise.all. Its what ive been using with drizzle too

Mario564•3mo ago

@ivan Hello. Running two queries is the most optimal, and as Shawn mentioned, you can use Promise.all to execute both the count query and pagination query at the same time. If you really want to keep everything in one query, then you can do this:

tab: getTableColumns(tab),
tab1: getTableColumns(tab1),
tab2: getTableColumns(tab2),
total: sql`(${db.select().from(total)})`.mapWith(Number).as('total')

tab: getTableColumns(tab),
tab1: getTableColumns(tab1),
tab2: getTableColumns(tab2),
total: sql`(${db.select().from(total)})`.mapWith(Number).as('total')

Gaming

Programming

How to get paginated result with the total count?