# Query — Building and Running Queries

`SMF\API\Query` is a small fluent builder. FROM/WHERE is written once and reused by every fetch mode; `LIMIT`, `COUNT`, and pagination are added automatically.

## Building a query

- `->from()` (with JOINs)
- `->where()`
- `->columns()` (default `*`)
- `->orderBy()`
- `->params()` (the `{int:...}`, `{query_see_board}`, etc. placeholders)

The builder itself can also run a query: `first()`, `exists()`, `fetchAll()` and `fetchPage($limit, $offset)` — the last one returns the page rows together with the grand total.

## Running via the controller helpers

- `$this->paginate($query)` — a collection with pagination meta; every row passes through `transform()`.
- `$this->first($query)` — a single row or `null` (for `show` and checks).
- `$this->exists($query)` — a `bool` (for validation in `store`).
- `$this->fetchAll($query)` — all rows without pagination (raw arrays).

## What's available in the controller

- `$this->request` — the `Request` object. It is read through accessors, not through public properties: `getMethod()`, `getSegments()`, `segment($index)`, `header($name)`, `query($name)`, `input($name)` and `all()` (the whole decoded body).
- `$this->response()` — a new `Response` (`->data()`, `->collection()`, `->status()`, `->header()`, `->error()`).
- `$this->paginate($query)` / `$this->first($query)` / `$this->exists($query)` / `$this->fetchAll($query)` — run a `Query`.
- `$this->transform($row)` — the output shape of a row; override it for your own resource (by default it returns the row as is).
- `$this->id($params)` — the integer `{id}`, validated; a second argument reads another path parameter, e.g. `$this->id($params, 'parent_id')`.
- `$this->pagination()` — `[limit, offset]` with sensible bounds (`limit` 1–100, default 25; `offset` default 0).
- `$this->cleanString($value)` — a safe string from the request body.
- `$this->writableColumns($schema, $full, $aliases)` — collect writable fields from the body; with `$full = true` (PUT) it requires every field in `$schema` that is not marked `optional`, with `false` (PATCH) it keeps only the ones sent (an empty PATCH → 400).
- `$this->boolean($value)` — coerce a body value to `bool` (accepts `true/false`, `1/0`, `"yes"/"no"`, `"on"/"off"`).
- `$this->noContent()` — an empty `204` response for successful operations without a body (usually `DELETE`).
- identity and permissions come from the global `$user_info` (set up by the `Authenticator`), and checks use the native `allowedTo()` / `$user_info['is_admin']`.

### The writable field schema

`writableColumns()` maps the body onto SMF columns through a schema — field name =\> description. Here is the schema of the built-in `categories` resource, so it can be compared with `CategoryController`:

schema

```php
<?php declare(strict_types=1);

private const WRITABLE = [
    'name'         => ['type' => 'string', 'column' => 'name', 'length' => 255],
    'description'  => ['type' => 'raw', 'column' => 'description'],
    'order'        => ['type' => 'int', 'column' => 'cat_order', 'optional' => true],
    'can_collapse' => ['type' => 'bool', 'column' => 'can_collapse', 'optional' => true],
];
```

| Key | Meaning |
| --- | --- |
| `type` | `string`, `raw`, `int`, `float`, `bool`, `email`, `date` or `int_list` |
| `column` | The SMF column to write; defaults to the field name |
| `min`, `max` | Bounds for integer fields; the default lower bound is `0`, and negative bounds allow negative values |
| `clamp` | Pin the value to the bound instead of rejecting it (SMF treats `min_posts = -1` as "not post-based") |
| `length` | Maximum length in characters for `string` and `raw` fields |
| `optional` | Accepted, but not required by a full replace |

The value types behave like this:

- `string` — trimmed and escaped with `htmlspecialchars()`;
- `raw` — trimmed, but kept as it is (message bodies, signatures, descriptions);
- `int` — a whole number inside the given bounds; decimals, leading zeros (`01`) and values with trailing characters are rejected;
- `float` — a number rounded to two decimals (SMF stores time offsets as floats);
- `bool` — `true`, `1`, `"yes"` or `"on"` are true, everything else is false;
- `email` — validated as an email address;
- `date` — `YYYY-MM-DD`; an empty value becomes SMF's "not set" date `1004-01-01`;
- `int_list` — a list of ids, given as `"2,7"` or `[2, 7]`, stored as `"2,7"`.

The third argument of `writableColumns()` is a list of aliases: SMF column names that clients may use instead of the field names, e.g. `['id_cat' => 'category']`. The alias is only used when the field itself was not sent.

> [!WARNING]
>
> **Writes go through SMF native functions**
>
> For mutating actions (`PUT` / `PATCH` / `DELETE`), perform the write through SMF's native functions (`modifyBoard`, `deleteMembers`, `modifyPost`, and so on) — `Query` is read-only. *Check permissions yourself* with `allowedTo()` before calling these functions and throw an `ApiException`: on denial, many core functions terminate the request with a fatal HTML error rather than JSON.

## Example: transform shape

Override `transform()` to define the outward shape of your resource — `paginate()` calls it for every row:

transform

```php
<?php declare(strict_types=1);

protected function transform(array $row): array
{
    return [
        'id'   => (int) $row['id_widget'],
        'name' => $row['name'],
    ];
}
```

See also [Adding a Resource](<https://dragomano.github.io/SMF-RESTful-API/guide/extending/adding-resource/index.md>) for the full controller example.
