Operators Reference

Every operator has a symbol — the prefix that appears immediately after ? in an expression. The filter parser matches operators longest-first, so !~~* is matched before !~~ and !~* before !~.

This page documents all built-in operators grouped by type.

Value Format Conventions

Placeholder	Meaning
`VALUE`	Any string value
`PATTERN`	A LIKE pattern (may contain `%` and `_`)
`DATE`	`YYYYMMDD` or `YYMMDD`
`MONTH`	`MM` or `M` (1–12)
`YEAR`	`YYYY` or `YY`
`PERIOD`	`YYYYMM` or `YYMM`
`INT`	Non-negative integer (no decimals)
`V1,V2`	Two comma-separated values
`V1,V2,…`	One or more comma-separated values
(empty)	No value; the symbol alone is the full filter

Standard Comparison Operators

Map directly to SQL comparison operators. Accept any string or numeric value.

Symbol	Name	Value	SQL Template
`=`	Equals	`VALUE`	`{{column}} = {{value}}`
`!=`	Not Equal	`VALUE`	`{{column}} != {{value}}`
`!`	Not Equal (shorthand)	`VALUE`	alias of `!=`
`<>`	Not Equal (SQL standard)	`VALUE`	alias of `!=`
`>=`	Greater Than or Equal	`VALUE`	`{{column}} >= {{value}}`
`<=`	Less Than or Equal	`VALUE`	`{{column}} <= {{value}}`
`>`	Greater Than	`VALUE`	`{{column}} > {{value}}`
`<`	Less Than	`VALUE`	`{{column}} < {{value}}`

Examples

price?=1000            →  price = :p             (:p = '1000')
status?!=archived      →  status != :p           (:p = 'archived')
total?>=500            →  total >= :p            (:p = '500')
created_at?<2025-01-01 →  created_at < :p        (:p = '2025-01-01')

Tip: Standard operators accept string operands for numbers, text and ISO dates. The database interprets them in the comparison context. For correlated COUNT, SUM and AVG comparisons, the SQL compiler explicitly casts PostgreSQL parameters to NUMERIC, including list/range operands. Thus COUNT(*)?>1.5 remains a decimal comparison, without truncating the value or converting it to a PHP float. The ORM bridge also uses mapped field types to preserve numeric versus text/date aggregate semantics.

AutoLike Operators (Automatic Pattern Generation)

These operators accept a plain text value and wrap it in % wildcards automatically before binding. They delegate to the like: / ilike: family for the actual SQL.

Starts With

Symbol	Case	Value	Bound value	SQL
`^`	Sensitive	`VALUE`	`VALUE%`	`col LIKE :p` (pgsql/sqlite), `col LIKE BINARY :p` (mysql)
`^*`	Insensitive	`VALUE`	`VALUE%`	`col ILIKE :p` (pgsql), `col LIKE :p` (mysql/sqlite)
`!^`	Sensitive	`VALUE`	`VALUE%`	`col NOT LIKE :p`
`!^*`	Insensitive	`VALUE`	`VALUE%`	`col NOT ILIKE :p` / `col NOT LIKE :p`

Contains

Symbol	Case	Value	Bound value	SQL
`~~`	Sensitive	`VALUE`	`%VALUE%`	`col LIKE :p`
`~~*`	Insensitive	`VALUE`	`%VALUE%`	`col ILIKE :p` / `col LIKE :p`
`!~~`	Sensitive	`VALUE`	`%VALUE%`	`col NOT LIKE :p`
`!~~*`	Insensitive	`VALUE`	`%VALUE%`	`col NOT ILIKE :p` / `col NOT LIKE :p`

Ends With

Symbol	Case	Value	Bound value	SQL
`$`	Sensitive	`VALUE`	`%VALUE`	`col LIKE :p`
`$*`	Insensitive	`VALUE`	`%VALUE`	`col ILIKE :p` / `col LIKE :p`
`!$`	Sensitive	`VALUE`	`%VALUE`	`col NOT LIKE :p`
`!$*`	Insensitive	`VALUE`	`%VALUE`	`col NOT ILIKE :p` / `col NOT LIKE :p`

Examples

name?^John      →  name LIKE :p         (:p = 'John%')
name?$*LLC      →  name ILIKE :p        (:p = '%LLC')    (pgsql)
description?~~keyword  →  description LIKE :p  (:p = '%keyword%')
title?!~~*draft →  title NOT ILIKE :p  (:p = '%draft%')  (pgsql)

Note on case sensitivity: PostgreSQL natively supports ILIKE; MySQL uses LIKE BINARY for case-sensitive and plain LIKE for case-insensitive; SQLite uses plain LIKE for both (SQLite’s LIKE is case-insensitive by default for ASCII).

Pattern (LIKE) Operators

Explicit LIKE operators where you supply the full pattern including % and _ wildcards.

Symbol	Case	Value	SQL
`like:`	Sensitive	`PATTERN`	pgsql: `col LIKE :p` · mysql: `col LIKE BINARY :p` · sqlite: `col LIKE :p`
`notlike:`	Sensitive	`PATTERN`	pgsql: `col NOT LIKE :p` · mysql: `col NOT LIKE BINARY :p`
`ilike:`	Insensitive	`PATTERN`	pgsql: `col ILIKE :p` · mysql: `col LIKE :p` · sqlite: `col LIKE :p`
`notilike:`	Insensitive	`PATTERN`	pgsql: `col NOT ILIKE :p` · mysql/sqlite: `col NOT LIKE :p`

Examples

email?like:%.example.com  →  email LIKE :p   (:p = '%.example.com')
name?ilike:jo_n%          →  name ILIKE :p   (:p = 'jo_n%')   (pgsql)
code?notlike:TEST_%       →  code NOT LIKE BINARY :p  (mysql)

For Doctrine ORM on MySQL, case-sensitive LIKE templates use the automatically registered DERAFU_MYSQL_BINARY_STRING function instead of embedding BINARY in DQL. The same applies to inherited auto-pattern operators.

Note: ilike: and notilike:, including aliases such as ^*, ~~*, $* and their negations, are not supported in Doctrine ORM DQL. They require SQL/DBAL/Illuminate compilation.

List Operators

Work with comma-separated lists of values. The list is split and each item becomes a separate named parameter.

Symbol	Value	SQL
`in:`	`V1,V2,…`	`{{column}} IN (:p1, :p2, …)`
`notin:`	`V1,V2,…`	`{{column}} NOT IN (:p1, :p2, …)`

Value Format

Values are split at every comma. The compiler does not unescape \, or treat it as a literal comma inside an item.

status?in:active,pending,review   →  status IN (:p1, :p2, :p3)

Although the validation pattern accepts \,, in:a\,b,c\,d binds four values: a\, b, c\ and d. Do not use backslash escaping to represent list items containing commas.

Validation

The value must contain at least one item. Each item may contain word characters, dots, hyphens, and Unicode letters/marks. Whitespace inside values is not supported.

Examples

status?in:paid,issued           →  status IN (:p1, :p2)
category?notin:archived,draft   →  category NOT IN (:p1, :p2)
id?in:1,2,3,4,5                 →  id IN (:p1, :p2, :p3, :p4, :p5)

Range Operators

Filter values between two bounds (inclusive) or outside them.

Symbol	Value	SQL
`between:`	`V1,V2`	`{{column}} BETWEEN {{value_1}} AND {{value_2}}`
`notbetween:`	`V1,V2`	`{{column}} NOT BETWEEN {{value_1}} AND {{value_2}}`

Value Format

Exactly two comma-separated values. Each value may contain word characters, dots, and hyphens (no spaces).

Examples

price?between:100,500      →  price BETWEEN :p1 AND :p2
price?notbetween:0,10      →  price NOT BETWEEN :p1 AND :p2
created_at?between:2024-01-01,2024-12-31  →  created_at BETWEEN :p1 AND :p2

Note: BETWEEN is inclusive on both ends. For exclusive ranges, use > and < separately.

Date Operators

Specialized operators for date and time filtering. They apply SQL date functions and normalize the value.

`date:` — Specific Date

date:YYYYMMDD   or   date:YYMMDD

Matches rows where the date portion of a datetime column equals a specific day.

Engine	SQL
All	`DATE({{column}}) = {{value}}`

Value normalization:

YYYYMMDD (8 digits) → YYYY-MM-DD
YYMMDD (6 digits) → 20YY-MM-DD

created_at?date:20240823   →  DATE(created_at) = :p   (:p = '2024-08-23')
created_at?date:240101     →  DATE(created_at) = :p   (:p = '2024-01-01')

`month:` — Month Number

month:MM   or   month:M

Matches rows in a specific calendar month, regardless of year.

Engine	SQL
All	`MONTH({{column}}) = {{value}}`

Value normalization: zero-padded to 2 digits (8 → 08).

created_at?month:08    →  MONTH(created_at) = :p   (:p = '08')
created_at?month:3     →  MONTH(created_at) = :p   (:p = '03')

`year:` — Year

year:YYYY   or   year:YY

Matches rows in a specific year.

Engine	SQL
All	`YEAR({{column}}) = {{value}}`

Value normalization: 2-digit years are expanded to 20YY.

created_at?year:2024   →  YEAR(created_at) = :p   (:p = '2024')
created_at?year:24     →  YEAR(created_at) = :p   (:p = '2024')

`period:` — Year and Month

period:YYYYMM   or   period:YYMM

Matches rows in a specific year+month.

Engine	SQL
pgsql	`TO_CHAR({{column}}, "YYYYMM") = {{value}}`
mysql	`DATE_FORMAT({{column}}, "%Y%m") = {{value}}`
sqlite	`strftime("%Y%m", {{column}}) = {{value}}`

Value normalization: 4-digit YYMM is expanded to 20YYMM.

created_at?period:202408   →  strftime("%Y%m", created_at) = :p   (:p = '202408')   (sqlite)
created_at?period:2408     →  …                                   (:p = '202408')

date:, month: and year: emit the universal SQL templates shown above; MONTH() and YEAR() are not rewritten for each engine. Database function availability still matters, so these templates alone do not guarantee PostgreSQL/SQLite portability.

Note: Date operators use SQL functions (DATE(), MONTH(), etc.) and are not compatible with Doctrine ORM DQL. Use the Doctrine DBAL or Illuminate bridges for date filtering.

NULL Operators

Symbol	Alias of	SQL
`is:null`	—	`{{column}} IS NULL`
`<=>`	`is:null`	`{{column}} IS NULL`
`isnot:null`	—	`{{column}} IS NOT NULL`

Value Format

The symbol is the complete filter — no value is expected after the symbol. is:null means the expression is literally column?is:null.

Examples

deleted_at?is:null      →  deleted_at IS NULL
deleted_at?<=>          →  deleted_at IS NULL    (alternative syntax)
email?isnot:null        →  email IS NOT NULL

Note: NULL operators produce no bound parameters.

Subquery Operators

Used exclusively with exists paths (___). They determine whether the correlated subquery checks for existence or absence.

Symbol	SQL
`is:empty`	`NOT EXISTS (SELECT 1 FROM … WHERE …)`
`isnot:empty`	`EXISTS (SELECT 1 FROM … WHERE …)`

___payments[on:id=invoice_id]?is:empty
→  NOT EXISTS (SELECT 1 FROM payments WHERE invoices.id = payments.invoice_id)

___payments[on:id=invoice_id]?isnot:empty
→  EXISTS (SELECT 1 FROM payments WHERE invoices.id = payments.invoice_id)

In Doctrine ORM DQL, a single collection association uses SIZE(alias.assoc) = 0 or SIZE(alias.assoc) > 0. Single-valued and nested associations use correlated NOT EXISTS / EXISTS based on their mappings.

Regular Expression Operators

These operators match POSIX extended regular expressions. Support varies by engine.

Core Operators

Symbol	Case	SQL (pgsql)	SQL (mysql)
`~`	Sensitive	`{{column}} ~ {{value}}`	`REGEXP_LIKE({{column}}, {{value}}, 'c')`
`~*`	Insensitive	`{{column}} ~* {{value}}`	`REGEXP_LIKE({{column}}, {{value}}, 'i')`
`!~`	Sensitive	`{{column}} !~ {{value}}`	`NOT REGEXP_LIKE({{column}}, {{value}}, 'c')`
`!~*`	Insensitive	`{{column}} !~* {{value}}`	`NOT REGEXP_LIKE({{column}}, {{value}}, 'i')`

SQLite does not have built-in regex support — these operators have no SQLite template and will fail if used against a SQLite connection.

MySQL templates use REGEXP_LIKE with explicit c (case-sensitive) or i (case-insensitive) matching. They target MySQL 8 and do not convert the operands to binary strings. These templates do not establish compatibility with MySQL 5.7 or MariaDB.

MySQL Aliases

Symbol	Alias of
`regexp:`	`~`
`notregexp:`	`!~`
`rlike:`	`~`
`notrlike:`	`!~`

These are provided for familiarity with MySQL’s SQL syntax:

name?regexp:^John   →  REGEXP_LIKE(name, :p, 'c')   (mysql)
name?rlike:John.*   →  REGEXP_LIKE(name, :p, 'c')   (mysql)

PostgreSQL SIMILAR TO

Symbol	SQL
`similarto:`	`{{column}} SIMILAR TO {{value}}`
`notsimilarto:`	`{{column}} NOT SIMILAR TO {{value}}`

SIMILAR TO uses SQL-standard regex patterns (a hybrid of LIKE wildcards and regex). It is PostgreSQL-specific.

Pattern rules: may contain %, _, [a-z], (a\|b), ?, *, +. Unbalanced (, ), [, ] are rejected at parse time.

name?similarto:John%           →  name SIMILAR TO :p
code?similarto:[0-9]+          →  code SIMILAR TO :p
status?notsimilarto:(a|b)%     →  status NOT SIMILAR TO :p

Examples

email?~@example\.com    →  email ~ :p   (pgsql)
name?~*john             →  name ~* :p   (pgsql) / REGEXP_LIKE(name, :p, 'i') (mysql)
phone?!~^\+             →  phone !~ :p  (pgsql)

Validation: Regex patterns must contain at least one non-metacharacter character to prevent trivially-matching expressions.

Note: Regex operators are not compatible with Doctrine ORM DQL.

Bitwise Operators

Operate on integer values at the bit level. All values must be non-negative integers (no decimals).

Symbol	Name	Value	SQL
`b&`	Bitwise AND	`INT`	pgsql/sqlite: `col & :p` · mysql: `BIT_AND(col, :p)`
`b\|`	Bitwise OR	`INT`	all: `col \| :p`
`b^`	Bitwise XOR	`INT`	pgsql: `col # :p` · mysql: `BIT_XOR(col, :p)` · sqlite: `(col \| :p) & ~(col & :p)`
`b<<`	Left Shift	`INT`	pgsql/sqlite: `col << :p` · mysql: `<< col, :p`
`b>>`	Right Shift	`INT`	pgsql/sqlite: `col >> :p` · mysql: `>> col, :p`
`b&~`	AND NOT	`INT`	all: `col & ~( :p )`

Common Use Case: Feature Flags

Bitwise AND is commonly used to test whether a flag bit is set in a bitmask column:

flags?b&1    →  flags & :p   (:p = '1')   — tests bit 0 (value 1)
flags?b&2    →  flags & :p   (:p = '2')   — tests bit 1 (value 2)
flags?b&4    →  flags & :p   (:p = '4')   — tests bit 2 (value 4)

Examples

permissions?b&4          →  permissions & :p   (:p = '4')   (pgsql)
permissions?b|3          →  permissions | :p   (:p = '3')
version?b^255            →  (version | :p) & ~(version & :p)   (sqlite)
offset?b<<2              →  offset << :p   (:p = '2')
mask?b&~8                →  mask & ~( :p )   (:p = '8')

Validation: Non-integer values (letters, decimals like 1.5) are rejected at parse time.

Note: Bitwise operators are not compatible with Doctrine ORM DQL.

Operator Aliases

Several operators are aliases that reuse another operator’s SQL template:

Symbol	Alias of	Reason
`!`	`!=`	Shorthand not-equal
`<>`	`!=`	SQL-standard not-equal
`<=>`	`is:null`	MySQL NULL-safe equality syntax
`^`	`like:`	auto-casts with `like_start`
`^*`	`ilike:`	auto-casts with `like_start`
`!^`	`notlike:`	auto-casts with `like_start`
`!^*`	`notilike:`	auto-casts with `like_start`
`~~`	`like:`	auto-casts with `like`
`~~*`	`ilike:`	auto-casts with `like`
`!~~`	`notlike:`	auto-casts with `like`
`!~~*`	`notilike:`	auto-casts with `like`
`$`	`like:`	auto-casts with `like_end`
`$*`	`ilike:`	auto-casts with `like_end`
`!$`	`notlike:`	auto-casts with `like_end`
`!$*`	`notilike:`	auto-casts with `like_end`
`regexp:`	`~`	MySQL familiarity
`notregexp:`	`!~`	MySQL familiarity
`rlike:`	`~`	MySQL familiarity
`notrlike:`	`!~`	MySQL familiarity

Casting Rules

Casting rules transform the raw value string before it is bound as a parameter:

Rule	Input	Output
`like_start`	`John`	`John%`
`like`	`John`	`%John%`
`like_end`	`Smith`	`%Smith`
`list`	`a,b,c`	`['a', 'b', 'c']` (split)
`date`	`20240823`	`2024-08-23`
`date`	`240823`	`2024-08-23`
`month`	`8`	`08`
`year`	`24`	`2024`
`period`	`202408`	`202408` (unchanged)
`period`	`2408`	`202408`

DQL Compatibility Matrix

The Doctrine ORM bridge generates DQL, which does not support all SQL features:

Operator type	DQL compatible?
Standard (`=`, `!=`, `>`, etc.)	✅
Case-sensitive AutoLike (`^`, `~~`, `$` and negations)	✅
`like:`, `notlike:`	✅
`ilike:`, `notilike:` and their auto-pattern aliases	❌
`in:`, `notin:`	✅
`between:`, `notbetween:`	✅
Date (`date:`, `month:`, `year:`, `period:`)	❌
NULL (`is:null`, `isnot:null`)	✅
Subquery (`is:empty`, `isnot:empty`)	✅ (SIZE for a single collection; otherwise EXISTS)
RegExp (`~`, `~*`, `similarto:`, etc.)	❌
Binary (`b&`, `b\|`, etc.)	❌

For incompatible operators, use the Doctrine DBAL bridge or IlluminateQueryBuilderConditionApplier instead. In API Platform’s SmartFilter, incompatible operators are silently skipped.

Custom Operators

Operators are defined in resources/operators.yaml. You can load a custom YAML file to add or override operators:

# my-operators.yaml
version: '1.0.0'
description: 'Custom operators'

types:
    standard:
        name: 'Standard Operators'
        description: 'SQL comparison operators'

operators:
    '=':
        type: standard
        name: Equals
        description: Exact match
        sql: '{{column}} = {{value}}'

    'startswith:':
        type: like
        name: Starts With (verbose)
        description: Explicit starts-with LIKE operator
        sql: '{{column}} LIKE {{value}}'
        cast: ['like_start']

use Derafu\Query\Operator\OperatorLoader;
use Derafu\Query\Operator\OperatorManagerFactory;

$factory = new OperatorManagerFactory(new OperatorLoader());
$manager = $factory->create('/path/to/my-operators.yaml');

Required fields per operator: type, name, description, all strings. The type key must reference an entry in the types section. Operator symbols must be strings. An alias must refer to an operator loaded earlier in the same file.

SQL Template Forms

sql accepts a universal string or a map keyed by engine. Each engine entry accepts a string or a map containing a required string default and an optional string dql:

sql:
    pgsql: '{{column}} LIKE {{value}}'
    mysql:
        default: '{{column}} LIKE BINARY {{value}}'
        dql: '{{column}} LIKE DERAFU_MYSQL_BINARY_STRING({{value}})'
    sqlite: '{{column}} LIKE {{value}}'

Configuration	SQL compilation	Doctrine ORM
Universal `sql` string	That string	That string
Engine entry is a string	That string	That string
Engine entry has variants	`default`	`dql` if present; otherwise `default`

Every engine entry is validated, including unselected engines. Unknown variant keys, deeper nesting, a missing/null default and a present non-string/null dql are rejected.

An absent or null sql inherits the base operator’s template. An explicitly provided map replaces that template; an engine missing from the map does not fall back to the base operator. Normal and aggregate conditions require an available non-empty template. An EXISTS column filter without a template leaves only the correlation condition.

A custom dql template must be valid DQL and any referenced custom functions must be registered with Doctrine. The built-in MySQL binary-string function is registered by the bridge.

Casting Validation

cast is an array of string rule names. Rules run in order. list produces an array; a later recognized rule requiring a string is rejected rather than applied to that array. Unknown rule names are ignored.

On this page

Last updated on 30/10/2026 by Anonymous

PDF

Markdown Copy

Path Syntax

Query Builder

Base Components for Libraries and Applications

Data Handling

Views, Templates and Rendering

Standards and Rules

Symfony Bundles

System Administration

Operators Reference

Value Format Conventions

Standard Comparison Operators

Examples

AutoLike Operators (Automatic Pattern Generation)

Starts With

Contains

Ends With

Examples

Pattern (LIKE) Operators

Examples

List Operators

Value Format

Validation

Examples

Range Operators

Value Format

Examples

Date Operators

date: — Specific Date

month: — Month Number

year: — Year

period: — Year and Month

NULL Operators

Value Format

Examples

Subquery Operators

Regular Expression Operators

Core Operators

MySQL Aliases

PostgreSQL SIMILAR TO

Examples

Bitwise Operators

Common Use Case: Feature Flags

Examples

Operator Aliases

Casting Rules

DQL Compatibility Matrix

Custom Operators

SQL Template Forms

Casting Validation

`date:` — Specific Date

`month:` — Month Number

`year:` — Year

`period:` — Year and Month