# Query Builder

````{note}
Not part of core. Install it separately:

```{code-block} sh
composer require kinetis/query-builder
```
````

A thin, parameterized SQL query builder over `amphp/mysql`/`amphp/postgres`
— not an ORM. No relationships, no migrations, no change-tracking, no
`save()`-on-a-model. It builds parameterized SQL and maps result rows into
typed DTOs via {doc}`routing-validation`'s `Hydrator` — the same mechanism
that hydrates a `#[Body]` request DTO.

```{code-block} php
use Kinetis\QueryBuilder\Query;

$orders = new Query($db)
    ->table('orders')
    ->where('customer_id', '=', $customerId)
    ->where('status', '!=', 'cancelled')
    ->orderBy('created_at', 'desc')
    ->limit(20)
    ->get(OrderRow::class);
```

## MySQL and Postgres

`Query` works with either backend through the same shared `Amp\Sql\SqlLink`
family both drivers implement, auto-detected from the concrete connection
you pass in:

```{code-block} php
new Query($mysqlDb);    // MySqlDialect
new Query($postgresDb); // PostgresDialect
new Query($db, new PostgresDialect()); // explicit override
```

## A different database: named connections

`Query` takes whatever connection you hand it — including one built for a
named connection via `Kinetis\Persistence\SqlConnectionFactory` (see
{doc}`persistence`, {doc}`config`):

```{code-block} php
use Kinetis\Persistence\SqlConnectionFactory;

$reporting = SqlConnectionFactory::fromConfig($config, 'db2');
$orders = new Query($reporting)->table('orders')->get(OrderRow::class);
```

Identifier quoting (backtick vs double-quote) and retrieving a generated
primary key after an `INSERT` (MySQL exposes it on the result; Postgres
needs `RETURNING`) are isolated in a small `Dialect` interface. Everything
else — parameterized `?` placeholders, `LIMIT n OFFSET m`, affected-row
counts — is identical between the two.

A qualified column name is quoted per segment: `orders.total` becomes
`` `orders`.`total` `` (or `"orders"."total"` on Postgres), not one literal
identifier containing a dot.

## Works inside `TransactionGuard`

`Query` accepts a plain connection pool or an in-flight
`Amp\Sql\SqlTransaction` — both satisfy the same interface:

```{code-block} php
$transactions->transaction($db, function ($db) use ($data) {
    new Query($db)->table('orders')->insert([...]);
    new Query($db)->table('inventory')
        ->where('sku', '=', $data->sku)
        ->update(['stock' => $newStock]);
});
```

See {doc}`persistence` for `TransactionGuard`'s commit/rollback behavior.

## Reading: `get()`, `first()`, `count()`

```{code-block} php
$rows = new Query($db)->table('users')->where('active', '=', true)->get();       // list<array<string, mixed>>
$rows = new Query($db)->table('users')->where('active', '=', true)->get(UserRow::class); // list<UserRow>
$user = new Query($db)->table('users')->where('id', '=', $id)->first(UserRow::class);    // UserRow|null
$total = new Query($db)->table('orders')->where('status', '=', 'paid')->count();          // int
```

Pass a DTO class and each row is hydrated through `Hydrator::hydrate()`,
constraints included (`#[Email]`, `#[MinLength]`, ...); omit it and you get
plain arrays.

## Pagination: `paginate()`, `cursorPaginate()`

Two ways to page through a result set, returning a plain value object a
controller can hand straight back — it encodes to JSON exactly like any
other `readonly` DTO, with no extra step:

```{code-block} php
#[Get('/orders')]
public function index(#[Query] int $page = 1, #[Query] int $perPage = 20): Paginator
{
    return new Query($this->db)->table('orders')->orderBy('id')->paginate($perPage, $page);
}
```

```{code-block} json
:caption: GET /orders?page=2&perPage=20

{
    "data": [{"id": 21, "...": "..."}, {"...": "..."}],
    "currentPage": 2,
    "perPage": 20,
    "total": 145,
    "lastPage": 8
}
```

`paginate(int $perPage, int $page = 1, ?string $dtoClass = null)` runs a
`count()` for `total` and a `limit()`/`offset()`-based `get()` for the
page itself — both against the same `where()`/`join()` filters already on
the query. A page past the last one returns an empty `data` array with
the real `total`/`lastPage` still reported, not an error.

Cursor-based pagination advances by the last row's own column value
instead of a page number, so rows inserted or deleted between requests
can't shift results the way an offset-based page number can — a better
fit for a large or fast-changing table:

```{code-block} php
#[Get('/orders')]
public function index(#[Query] ?string $cursor = null): CursorPaginator
{
    return new Query($this->db)->table('orders')->cursorPaginate(perPage: 20, cursor: $cursor);
}
```

```{code-block} json
:caption: GET /orders, then GET /orders?cursor=145

{"data": ["...", "..."], "nextCursor": "165", "hasMore": true}
```

`cursorPaginate(int $perPage, ?string $cursor, string $cursorColumn = 'id', ?string $dtoClass = null)`
orders the query by `$cursorColumn` itself and filters
`WHERE $cursorColumn > $cursor` once a cursor is given — `null` (the first
call) fetches from the start. The cursor is the column's own raw value,
not an encoded token; nothing here is sensitive, so there's no reason to
obscure it. There's no total count and no page number — that's the actual
tradeoff for avoiding `COUNT(*)` on a table where that query would be
expensive, and it means a client can't jump to an arbitrary page, only
"give me the next one."

```{warning}
`cursorPaginate()` always orders by `$cursorColumn`. Adding your own
`orderBy()` call on a *different* column can make it skip or repeat rows —
the `WHERE $cursorColumn > ?` comparison only makes sense against the
column the results are actually ordered by.
```

Neither method caps `$perPage` — a request for `?perPage=1000000` is
passed straight through. Capping it, if your application needs one, is a
normal application-level concern (clamp it in the controller before
calling either method), the same way `Query` doesn't validate a `where()`
value either.

### Describing the item shape in OpenAPI

`Paginator`/`CursorPaginator` are the same two classes for every paginated
route, regardless of what each one actually holds, so the generated
OpenAPI document describes `data` as a bare object by default —
reflecting the return type alone can't recover what's inside it.
`#[PaginatedItem]` names it explicitly:

```{code-block} php
use Kinetis\Http\Attributes\PaginatedItem;

#[Get('/orders')]
#[PaginatedItem(OrderResponse::class)]
public function index(#[Query] int $page = 1, #[Query] int $perPage = 20): Paginator
{
    return new Query($this->db)->table('orders')->orderBy('id')->paginate($perPage, $page);
}
```

`data` now describes as an array of `OrderResponse`'s own schema,
deduplicated into `components/schemas` the same way a nested DTO already
is. Purely descriptive — nothing checks that the route actually returns
that item type at runtime, the same trust already placed in
`#[Response(status, description)]`'s own status code.

## Writing: `insert()`, `insertGetId()`, `update()`, `delete()`

```{code-block} php
new Query($db)->table('users')->insert(['email' => $email, 'name' => $name]);

$id = new Query($db)->table('users')->insertGetId(['email' => $email], primaryKey: 'id');

$affected = new Query($db)->table('users')->where('id', '=', $id)->update(['name' => $newName]);

$deleted = new Query($db)->table('users')->where('id', '=', $id)->delete();
```

`update()`/`delete()` return the affected-row count.

## Raw SQL

A plain `SqlLink`/`SqlTransaction` and `$db->execute(...)` — see
{doc}`persistence` — bypasses the builder entirely with no special support
needed.

For raw fragments inside an otherwise-fluent query:

```{code-block} php
new Query($db)->table('orders')
    ->selectRaw('COUNT(*) as total, DATE(created_at) as day')
    ->whereRaw('YEAR(created_at) = ?', [2026])
    ->orderByRaw('RAND()')
    ->get();
```

```{danger}
`whereRaw()`'s `$params` are bound as real parameters, in the exact
position their `?` appears in `$sql` — never string-interpolated. Building
`$sql` by concatenating a user-controlled value instead of passing it
through `$params` reintroduces exactly the injection risk parameterized
queries exist to prevent.
```

## Parameter order

Structured `where()` calls, `whereIn()`, and `whereRaw()` fragments can all
be mixed in one query; their bound values always appear in the same order
as the `?` placeholders in the generated SQL:

```{code-block} php
new Query($db)->table('orders')
    ->where('customer_id', '=', 7)
    ->whereRaw('YEAR(created_at) = ?', [2026])
    ->whereIn('status', ['pending', 'paid'])
    ->where('total', '>', 100)
    ->get();
// WHERE `customer_id` = ? AND YEAR(created_at) = ? AND `status` IN (?, ?) AND `total` > ?
// params: [7, 2026, 'pending', 'paid', 100]
```

```{warning}
One `Query` instance is one query. `table()`/`select()`/`where()`/...
mutate and accumulate on the same instance — nothing resets between calls.
Construct a fresh `new Query($link)` per query; reusing one instance
across separate queries merges their `where()`s together.
```

## See also

- {doc}`persistence` — connecting to MySQL/Postgres, `TransactionGuard`,
  and caching query results.
- {doc}`routing-validation` — more on `Hydrator`, including nested-DTO
  support.