How to create a generic function that creates select query with the provided fields?

[romario333]

I need to run the same query twice for pagination:

• First query: to get the total row count.
• Second query: To retrieve data for the current page, which involves ordering the data and applying limit and offset.

The core of the query is identical in both cases, differing only in the fields they select and limit being applied. To avoid repeating the query logic I have created a function to generate it:

function query(fields: any): any {
  return db
    .select(fields)
    .from(hotels)
    .innerJoin(cities, eq(cities.id, hotels.city_id))
    .where(eq(hotels.locale, 'cs'));
}

const total = await query({ count: count() });

const rows = await query({
  id: hotels.id,
  cityId: cities.id,
  name: hotels.name,
})
  .orderBy(hotels.id)
  .limit(limit)
  .offset(offset);

This approach works, but I lose type safety. I haven’t figured out how to properly type the query function. Does anyone have any ideas how to make this work, or how to approach this problem differently?

[nicholasdly]

Not exactly the same implementation you were going for, but Drizzle has the following example in their documentation under "Guides" for implementing reusable pagination logic:

import { SQL, asc } from 'drizzle-orm';
import { PgColumn, PgSelect } from 'drizzle-orm/pg-core';

function withPagination<T extends PgSelect>(
  qb: T,
  orderByColumn: PgColumn | SQL | SQL.Aliased,
  page = 1,
  pageSize = 3,
) {
  return qb
    .orderBy(orderByColumn)
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

const query = db.select().from(users); // query that you want to execute with pagination

await withPagination(query.$dynamic(), asc(users.id));

Source: https://orm.drizzle.team/docs/guides/limit-offset-pagination