# Filters
A filter is a core `iFilter` — the same structure the whole framework uses — and `PDOPrinter` turns it into a
`WHERE` clause. You never write SQL for it, and every value is bound.
```php
$table->select('*', ['name' => 'marc']);
$collection->query(['status' => 'active', 'age' => ['$gte' => 18]]);
$statement->setFilter(['$or' => [['a' => 1], ['b' => ['$gt' => 2]]]]);
```
A plain `'column' => value` is equality. Operators are `$`-prefixed keys.
## Operators
Every operator of core's `Filter`, so a `Query` returns from a table what it returns in memory
(`FilterOperator::test`): `TestPDOQueries` runs each filter both ways and compares.
| filter | SQL |
|---|---|
| `['a' => 1]` | `` (`a` = ?) `` |
| `['a' => ['$ne' => 1]]` | `` (`a` <> ? OR `a` IS NULL) `` — a null is not 1 |
| `['a' => ['$gt' => 1]]` | `` (`a` > ?) `` |
| `['a' => ['$gte' => 1]]` | `` (`a` >= ?) `` |
| `['a' => ['$lt' => 1]]` | `` (`a` < ?) `` |
| `['a' => ['$lte' => 1]]` | `` (`a` <= ?) `` |
| `['a' => ['$in' => [1, 2]]]` | `` (`a` IN (?,?)) `` — one binding per item, its keys ignored; `OR a IS NULL` when the list holds null; `(1=0)` when it is empty |
| `['a' => ['$nin' => [1, 2]]]` | `` (`a` NOT IN (?,?) OR `a` IS NULL) `` — without the `IS NULL` when the list holds null |
| `['a' => ['$exists' => true]]` | `` (`a` IS NOT NULL) `` — and `IS NULL` for `false` (or `'false'`, `'0'`) |
| `['a' => ['$text' => 'x']]` | `` (INSTR(`a`, ?) > 0) `` — the text is in the value |
| `['a' => ['$regex' => '/^x/i']]` | `` (`a` REGEXP ?) `` — see below |
| `['a' => ['$has' => 'x']]` | the column holds a JSON list that has the value — see below |
| `['meta/lang' => 'fr']` | the value at `$."lang"` in the JSON of `meta` — see below |
| `'(a > {#b})'` | `` (`a` > `b`) `` — another column of the same row |
| `['$and' => [x, y]]` | `(x AND y)` |
| `['$or' => [x, y]]` | `(x OR y)` |
| `['$nor' => [x, y]]` | the negation of each condition, combined (see `$nor`) |
Several keys in one filter are AND-ed. A date (`DateTime`) is bound as `'Y-m-d H:i:s'`, an enum as its case name, an
entity as its id.
`null` is compared with `IS`, never with `=`: `['a' => null]` becomes `` (`a` IS NULL) ``, and
`['a' => ['$ne' => null]]` becomes `` (`a` IS NOT NULL) ``. (`col = NULL` is never true in SQL, not even for a
null column.) A condition matches a null column when `FilterOperator::test` matches a null — `$ne`, `$nin`, the
negation of the others — with one exception: `$lt`/`$lte` do not, although PHP takes null for less than anything.
### `$regex`
The pattern is read as `FilterOperator::test` reads it: `/…/flags`, or a text that becomes an accent and case
insensitive pattern (`'miserables'` finds `Les Misérables`).
- **SQLite** has `REGEXP` but no function behind it: `SqliteHandler::connect()` gives it `preg_match()`, so the
pattern is PHP's, exactly.
- **MySQL** takes the pattern without its delimiters, its `i`, `m`, `s` and `x` flags written inline (`(?i)^x`),
which MySQL 8 (ICU) and MariaDB (PCRE) both read.
### `$has` and nested paths: JSON
A list or a map is stored as JSON (`PDOExporter`). `$has` asks whether the list holds a value: `json_each()` in
SQLite, `JSON_CONTAINS()` in MySQL, a column that is not valid JSON matching nothing. A nested path reads the JSON:
`meta/tags/0` is `$."tags"[0]` (`json_extract()`, `JSON_UNQUOTE(JSON_EXTRACT())` in MySQL). Its segments are written
into the query, so they are limited to letters, digits, `_`, `-` and spaces; anything else is refused.
A nested path whose first segment is a **reference** (`author/name`, where `author` holds the id of another table's
row) is a subquery — see [Repository](Repository.md#filters).
Every check is made **when the filter is set**, not when it is rendered. That matters: the printer's own
exceptions are caught by `TextPrinter::__invoke` and returned as inline text, so a filter checked only at
render time would end up pasted into the query — stack trace and all — instead of failing.
## Columns
A column is quoted segment by segment, so a qualified name from a join comes out right:
```php
['t1.id' => 1] // (`t1`.`id` = ?) -- not (`t1.id` = ?)
```
An empty filter group renders as nothing at all — no `WHERE` — rather than `WHERE ()`.
## `$nor`
`$nor` is implemented by inverting each operator as the tree is walked (`=` becomes `<>`, `<` becomes `>=`, …)
rather than by wrapping the group in `NOT`, which a null would make neither true nor false. The members of a group
are inverted too: `!(a AND b)` is `!a OR !b` — a filter's several keys included —, and a `$nor` inside a `$nor` is
an `OR` of its members.