# 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
| filter | SQL |
|---|---|
| `['a' => 1]` | `` (`a` = ?) `` |
| `['a' => ['$ne' => 1]]` | `` (`a` <> ?) `` |
| `['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 |
| `['a' => ['$nin' => [1, 2]]]` | `` (`a` NOT IN (?,?)) `` |
| `['a' => ['$exists' => true]]` | `` (`a` IS NOT NULL) `` — and `IS NULL` for `false` |
| `['$and' => [x, y]]` | `(x AND y)` |
| `['$or' => [x, y]]` | `(x OR y)` |
| `['$nor' => [x, y]]` | the negation of each condition, combined |
Several keys in one filter are AND-ed.
`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.)
## Not supported
| filter | why |
|---|---|
| `$regex` | no portable SQL equivalent (MySQL `REGEXP`, SQLite needs a user function) |
| `$has` | no SQL equivalent |
| `$text` | full-text search is not implemented |
| `'a/b' => …` (a nested path) | that is a join, and nothing here knows how the tables relate — see [Joins](Joins.md) |
Each raises a clear exception **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`. It works for the operators above; it is the least exercised part
of the translation, and the source carries a `@TODO` about revisiting it.