# Sorting and Pagination

How to sort results and paginate through large datasets using OrderBy, OrderByDesc, Limit, and Offset in Socigy.OpenSource.DB v0.1.83.

## Overview

The query builder exposes `.OrderBy()`, `.OrderByDesc()`, `.Limit()`, and `.Offset()` for controlling sort order and result paging. `.OrderBy()` and `.OrderByDesc()` are available on regular queries only. `.Limit()` and `.Offset()` work on regular queries and JOIN queries; set-operation builders support `.Limit()` and `.Offset()` on the combined result but not sorting.

## Sorting

### Ascending Order

Pass an array of columns to `.OrderBy()`. PostgreSQL sorts all listed columns in ascending order.

```csharp
// C# — sort by Name ascending, then by CreatedAt ascending
var users = await User.Query()
    .OrderBy(x => new object[] { x.Name, x.CreatedAt })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "name" ASC, "created_at" ASC
```

### Descending Order

```csharp
// C# — sort by Score descending
var topScorers = await User.Query()
    .OrderByDesc(x => new object[] { x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC
```

### Combining Ascending and Descending

Chain multiple `.OrderBy()` and `.OrderByDesc()` calls. Columns are appended to the `ORDER BY` clause in the order the calls appear.

```csharp
// C# — sort by Score DESC, then by Name ASC
var results = await User.Query()
    .OrderByDesc(x => new object[] { x.Score })
    .OrderBy(x => new object[] { x.Name })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC, "name" ASC
```

## Pagination with Limit and Offset

### Limit

`.Limit(n)` emits a SQL `LIMIT` clause and caps the number of rows returned.

```csharp
// C# — fetch at most 20 rows
var page = await User.Query()
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

### Offset

`.Offset(n)` emits a SQL `OFFSET` clause and skips the first `n` rows of the result. It is almost always used together with `.Limit()`.

```csharp
// C# — fetch page 3 (zero-based pages of 20)
int pageSize = 20;
int pageNumber = 2; // 0-indexed → page 3

var page3 = await User.Query()
    .OrderBy(x => new object[] { x.CreatedAt })
    .Limit(pageSize)
    .Offset(pageNumber * pageSize)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "created_at" ASC LIMIT 20 OFFSET 40
```

> **WARNING** Always pair `Offset` with `OrderBy`. Without a deterministic sort order the database is free to return rows in any sequence, making page boundaries unpredictable and potentially causing rows to appear on multiple pages or not at all.

## Cursor-Based Pagination

Offset pagination becomes slow on large tables because PostgreSQL must scan and skip the first `n` rows on every request. Cursor-based pagination avoids this by filtering on the last-seen key value.

```csharp
// C# — cursor-based next-page using the last seen Id
Guid lastSeenId = previousPage.Last().Id;

var nextPage = await User.Query(x => x.Id > lastSeenId)
    .OrderBy(x => new object[] { x.Id })
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

> **TIP** Cursor-based pagination requires a stable, sortable, unique column (like an auto-incrementing integer or a time-ordered UUID). It does not support jumping to arbitrary pages, but it scales to billions of rows where `OFFSET` would time out.

## Full Pagination Example

```csharp
// C# — typical paged API endpoint
async Task<List<User>> GetPageAsync(DbConnection conn, int page, int pageSize)
{
    return await User.Query(x => x.DeletedAt == null)
        .OrderByDesc(x => new object[] { x.CreatedAt })
        .OrderBy(x => new object[] { x.Id })
        .Limit(pageSize)
        .Offset(page * pageSize)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync();
}
```

## Pagination on Set Operations

`.Limit()` and `.Offset()` are available on set-operation builders (Union, Intersect, Except, etc.) and apply to the combined result. Sorting on the combined result is not supported — apply `OrderBy` to each sub-query individually before combining, or sort in C# after materializing.

```csharp
// C# — paginate a UNION result
var lhs = User.Query(x => x.Active == true);
var rhs = User.Query(x => x.IsPremium == true);

var combined = await lhs.Union(rhs)
    .Limit(20)
    .Offset(0)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## See Also

- [SELECT / Query](select) — full query builder reference
- [WHERE Operators](where) — filtering rows before sorting
- [Set Operations](set-operations) — Union, Intersect, Except with pagination