A pagination helper

import { sql, type SelectQueryBuilder, type StringReference } from 'kysely';

export async function paginateQuery<DB, TB extends keyof DB, O>(
    query: SelectQueryBuilder<DB, TB, O>,
    options: {
        offset?: number;
        limit?: number;
        countColumn?: StringReference<DB, TB>;
    }
) {
    const { offset = 0, limit = 10, countColumn = 'id' } = options;

    const countResult = await query
        .clearSelect()
        .clearLimit()
        .clearOffset()
        .clearGroupBy()
        .clearOrderBy()
        .select([sql<number>`COUNT(DISTINCT ${sql.ref(countColumn)})`.as('count')])
        .$castTo<{ count: number }>()
        .executeTakeFirst();

    const count = Number(countResult?.count ?? 0);

    const rows = await query.limit(limit).offset(offset).execute();

    const currentPage = Math.floor(offset / limit) + 1;
    const totalPages = Math.ceil(count / limit);

    return {
        rows,
        count,
        totalPages,
        currentPage,
    };
}

import { sql, type SelectQueryBuilder, type StringReference } from 'kysely';

export async function paginateQuery<DB, TB extends keyof DB, O>(
    query: SelectQueryBuilder<DB, TB, O>,
    options: {
        offset?: number;
        limit?: number;
        countColumn?: StringReference<DB, TB>;
    }
) {
    const { offset = 0, limit = 10, countColumn = 'id' } = options;

    const countResult = await query
        .clearSelect()
        .clearLimit()
        .clearOffset()
        .clearGroupBy()
        .clearOrderBy()
        .select([sql<number>`COUNT(DISTINCT ${sql.ref(countColumn)})`.as('count')])
        .$castTo<{ count: number }>()
        .executeTakeFirst();

    const count = Number(countResult?.count ?? 0);

    const rows = await query.limit(limit).offset(offset).execute();

    const currentPage = Math.floor(offset / limit) + 1;
    const totalPages = Math.ceil(count / limit);

    return {
        rows,
        count,
        totalPages,
        currentPage,
    };
}

const paginatedResult = await paginateQuery(query, { countColumn: 'product.id', limit: 20, offset: 10 });

const paginatedResult = await paginateQuery(query, { countColumn: 'product.id', limit: 20, offset: 10 });