Drizzle TeamDT
Drizzle Team•2y ago
ivan

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


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.
Was this page helpful?