K
Kysely•5w ago
yayza_

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 });
3 Replies
yayza_
yayza_OP•5w ago
lmk if i can make it better.. i'm still learning 🙂
Unknown User
Unknown User•3w ago
Message Not Public
Sign In & Join Server To View
yayza_
yayza_OP•3w ago
i tried to do that, but I ended up with an error because my main query uses a subquery, so I had to keep select clause too, 
    const subQuery = query.clearLimit().clearOffset();
    const countResult = await db
        .selectFrom(subQuery)
        .select(sql<number>`COUNT(*)`.as('count'))
        .executeTakeFirst();
    const subQuery = query.clearLimit().clearOffset();
    const countResult = await db
        .selectFrom(subQuery)
        .select(sql<number>`COUNT(*)`.as('count'))
        .executeTakeFirst();
but that means my main query would select rows and run agg functions again 🤔 just to get a count. i tested both queries and i'm getting same counts so far but i'll keep trying to do it like you mentioned because i definitely don't want removing groupBy to have an unintended effect

Did you find this page helpful?