Query Builder - Sorting and Paging - Paging

Overview

Introduction

Sorting and Paging

PagingInput Interface #

Property	Type
limit?	`number`
Maximum number of rows to return. Default: `100`
offset?	`number`
Number of rows to skip before fetching.

Trino Pagination Syntax #

Trino uses SQL standard FETCH FIRST N ROWS ONLY syntax rather than the MySQL-style LIMIT clause. The query builder generates two different patterns depending on whether an offset is provided.

Without Offset #

When offset is not set or is 0, the query uses:

1
2
FETCH FIRST 25 ROWS ONLY

With Offset #

When offset is a positive number, the query uses:

1
2
3
OFFSET 50
FETCH NEXT 25 ROWS ONLY

Note the difference: FETCH FIRST becomes FETCH NEXT when an offset is present.

Usage Example #

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32import { generateQuery } from "@lakeql/query-builder"

// Example table type
interface MyTable {
  id: number
  event_type: string
  timestamp: string
  user_id: number
}

// First page: 25 rows, no offset
const page1 = generateQuery<MyTable>({
  catalog: "hive",
  schema: "analytics",
  table: "events",
  selectFields: ["id", "event_type", "timestamp"],
  userQuery: {},
  sorting: [{ field: "timestamp", direction: "desc" }],
  paging: { limit: 25 },
})

// Second page: next 25 rows
const page2 = generateQuery<MyTable>({
  catalog: "hive",
  schema: "analytics",
  table: "events",
  selectFields: ["id", "event_type", "timestamp"],
  userQuery: {},
  sorting: [{ field: "timestamp", direction: "desc" }],
  paging: { limit: 25, offset: 25 },
})

Default Behavior #

If no paging option is provided, the query builder defaults to FETCH FIRST 100 ROWS ONLY with no offset. This prevents unbounded result sets from being returned accidentally.