Besides filtering, generateQuery takes sorting and paging inputs. They control the ORDER BY and the row window of the generated SQL.
Sorting #
Sorting is an array of SortInput entries, applied in order. Each entry names a field and a direction:
1
2
3
4
5
6
7
import type { SortInput } from "@lakeql/query-builder"
const sorting: SortInput<OrdersTable>[] = [
{ field: "status", direction: "asc" },
{ field: "createdAt", direction: "desc" },
]
The field type is constrained to the columns of the table definition you pass as the type parameter. An invalid column name is a compile-time error.
sorting is a required argument. A
deterministic ORDER BY is the only way a SQL engine guarantees a stable row
order across pages. Without it, offset-based paging can repeat or skip rows
between requests, so the same page can return different results. This is not
engine-specific. It was first observed with Trino, but any engine applying
LIMIT/OFFSET without an ORDER BY can exhibit it. That is why the
generated queries always require at least one sort field.Paging #
Paging is offset-based, via PagingInput:
1
2
3
4
5
6
7
import type { PagingInput } from "@lakeql/query-builder"
const paging: PagingInput = {
limit: 20, // max rows to return — defaults to 100
offset: 40, // rows to skip before fetching
}
limit caps the number of returned rows and defaults to 100 when omitted. offset skips rows before the window. Omit it, or pass 0, for the first page. The adapter's dialect renders the correct pagination syntax for the target engine.
Total Count #
The generated query reports the total number of matching rows independently of the page window. generateQuery builds two CTEs. One counts all matching rows, the other selects the current page. A single round trip then returns both the page and the total used for page-info metadata.