Mirror of https://github.com/roostorg/coop
github.com/roostorg/coop
1import * as knexPkg from 'knex';
2import type { Knex } from 'knex';
3
4const { knex } = knexPkg.default;
5
6/**
7 * When paginating backwards (i.e., the user's on page 5 and asks for page 4
8 * by requesting `last: n, before: "firstCursorOnPage5"`), we need to take
9 * results _from the end_. E.g., imagine the paginated collection is
10 * [1, 2, 3, 4, 5], and let's say each page has 2 items. If the user starts on
11 * the last page ([4, 5]), then the prior page should be [2, 3]. This means
12 * first filtering the the results to exclude those that are >= 4, but then
13 * taking two items _from the end_ of those results that remain.
14 *
15 * However, SQL doesn't have a "take from the end" operation (i.e., LIMIT always
16 * takes from the start, and drops the remaining). So, to implement "take the
17 * last 2" generically, you need to sort the items in reverse order, apply a
18 * LIMIT, then reverse the result.
19 *
20 * To do that generically, we need to use a query builder that'll let us build
21 * queries programmatically/work with them as data structures, so we use knex.
22 * This function, then implements the "take last n" operation given an unsorted
23 * knex select query and some sort criteria.
24 *
25 * @param unsortedSelectQuery The query that selects the set of items (without
26 * them being sorted) from which we want to take the last n, after sorting.
27 *
28 * @param sortCriteria The criteria that should be used to sort the items
29 * returned by unsortedSelectQuery, before we can take the last n items.
30 * NB: the column names provided here must refer to one of the columns
31 * selected by `unsortedSelectQuery`, under that column's final alias. E.g.,
32 * if unsortedSelectQuery is `SELECT a as "hello" from table`, then you can
33 * only provide "hello" as the sort criteria; not "a", and not some unselected
34 * column "b".
35 *
36 * @param size How many items to take.
37 *
38 * @param client The name of the knex client to use. This effects the
39 * SQL-dialect-specific settings that knex might apply to the generated query.
40 * NB: these dialect-specific settings potentially include data escaping rules
41 * that could be relevant for SQL injection.
42 *
43 * @param subqueryAlias Internally, this query generates a subquery, and SQL
44 * mandates that that subquery be given an alias. In theory, there's maybe
45 * some risk of that alias
46 *
47 * @returns A new knex query that selects the last n items, after sorting.
48 */
49export function takeLast<T extends object>(
50 unsortedSelectQuery: Knex.QueryBuilder<T>,
51 sortCriteria: {
52 column: (keyof T & string) | Knex.Raw;
53 order: 'desc' | 'asc';
54 }[],
55 size: number,
56 client: string = 'pg',
57) {
58 // SQL requires that the subquery we create have an alias. I don't _think_
59 // there's risk of that name causing a naming conflict, but I haven't thought
60 // too hard about all the scoping implications, so, to be safe, we give this
61 // alias a very-unlikely-to-conflict name.
62 const subqueryAlias = 'dc2d41a9-082e-48b0-a66f-345a22696b02';
63
64 const inner = unsortedSelectQuery
65 .clone()
66 .orderBy(
67 sortCriteria.map((it) => ({
68 // Cast here is because I think the knex typings are just wrong.
69 // They suggest that `column` has to be a string, but, actually,
70 // we can sort on arbitrary expressesions contained in a `knex.raw`.
71 column: it.column as keyof T & string,
72 order: it.order === 'desc' ? ('asc' as const) : ('desc' as const),
73 })),
74 )
75 .limit(size);
76
77 return knex({ client })
78 .select('*')
79 .from<T>(inner.as(subqueryAlias))
80 .orderBy(
81 // Cast here is same as above.
82 sortCriteria as ((typeof sortCriteria)[number] & { column: string })[],
83 );
84}