Mirror of https://github.com/roostorg/coop
github.com/roostorg/coop
1import type { Kysely, SelectQueryBuilder } from 'kysely';
2
3/**
4 * When paginating backwards (i.e., the user's on page 5 and asks for page 4
5 * by requesting `last: n, before: "firstCursorOnPage5"`), we need to take
6 * results _from the end_. E.g., imagine the paginated collection is
7 * [1, 2, 3, 4, 5], and let's say each page has 2 items. If the user starts on
8 * the last page ([4, 5]), then the prior page should be [2, 3]. This means
9 * first filtering the the results to exclude those that are >= 4, but then
10 * taking two items _from the end_ of those results that remain.
11 *
12 * However, SQL doesn't have a "take from the end" operation (i.e., LIMIT always
13 * takes from the start, and drops the remaining). So, to implement "take the
14 * last 2" generically, you need to sort the items in reverse order, apply a
15 * LIMIT, then reverse the result.
16 *
17 * This function implements that pattern on a Kysely select query.
18 *
19 * @param db Kysely instance used only to build the outer `select * from (…)`.
20 * It must use the same dialect as `unsortedSelectQuery`.
21 *
22 * @param unsortedSelectQuery The query that selects the set of items (without
23 * them being sorted) from which we want to take the last n, after sorting.
24 *
25 * @param sortCriteria The criteria that should be used to sort the items
26 * returned by unsortedSelectQuery, before we can take the last n items.
27 * NB: the column names provided here must refer to one of the columns
28 * selected by `unsortedSelectQuery`, under that column's final alias. E.g.,
29 * if the select is `DS as date`, then sort criteria must use `date`, not
30 * `DS`.
31 *
32 * @param size How many items to take.
33 *
34 * @returns A Kysely query that selects the last n items, after sorting.
35 */
36const SUBQUERY_ALIAS = 'dc2d41a9-082e-48b0-a66f-345a22696b02';
37
38export function takeLast<
39 DB,
40 TB extends keyof DB,
41 O extends Record<string, unknown>,
42>(
43 db: Kysely<DB>,
44 unsortedSelectQuery: SelectQueryBuilder<DB, TB, O>,
45 sortCriteria: readonly { column: keyof O & string; order: 'desc' | 'asc' }[],
46 size: number,
47) {
48 let inner = unsortedSelectQuery.clearOrderBy();
49 for (const it of sortCriteria) {
50 inner = inner.orderBy(
51 it.column,
52 it.order === 'desc' ? 'asc' : 'desc',
53 );
54 }
55 inner = inner.limit(size);
56
57 // Chaining `orderBy` in a loop widens `outer` to an incompatible union; the
58 // builder is still the same concrete Kysely select at runtime.
59 let outer = db.selectFrom(inner.as(SUBQUERY_ALIAS)).selectAll() as SelectQueryBuilder<
60 DB & { [K in typeof SUBQUERY_ALIAS]: O },
61 typeof SUBQUERY_ALIAS,
62 O
63 >;
64 for (const it of sortCriteria) {
65 outer = outer.orderBy(it.column, it.order) as typeof outer;
66 }
67 return outer as SelectQueryBuilder<
68 DB & { [K in typeof SUBQUERY_ALIAS]: O },
69 typeof SUBQUERY_ALIAS,
70 O
71 >;
72}