Best way possible to get records with limit and offset along side with total records in Postgresql

[Senbonzakura1234]

Currently to filter records with limit-offset and get total count for it, i have to make 2 separate queries, one to count all records select count(*) AS total_records FROM "table", and another to get current page records select * from "table" limit 10 offset 10;.

const [count, records] = await Promise.all([
  ctx.db.select({ count: sql<number>`count(*)` }).from(tables).where(whereQueries),
  ctx.db.select().from(tables).where(whereQueries).orderBy(orderQueries).limit(10).offset(10),
])

Which i think is not optimal at all, is there a way to get the results of the 2 queries above in 1 single query using drizzle? I found a solution but it is just raw SQL and i want to do this in drizzle way

[Senbonzakura1234]

i found a way by doing this below

const data = await ctx.db.select({ record: table, count: sql<number>`count(*) over()` })
  .from(table).where(...).limit(10).offset(0);

But it make the shape of the result became:

type results = {
  record: Record;
  count: number;
}[]

That make each record item contain the total count,
which i want it to be separated like this:

type results = {
  records: Record[];
  count: number;
}

How can i do it?

[Angelelz]

You'll need to change the shape yourself as aggregations are not supported (yet?) in Relational queries. For example:

const newShape = {
  records: results.map(result => result.record),
  count: results[0].count
}