WHERE DSL Reference

This reference is for Pionia Shop developers who prefer Medoo-style WHERE arrays over fluent where() when building ProductService filters. Every operator below applies to products, projects, and customers queries on the shop’s port 8000 stack.

What you will learn#

• Write comparison, LIKE, IN, and NULL operators in WHERE arrays
• Nest AND / OR groups for complex board filters
• Embed raw SQL fragments safely with Piql::raw()

How it works#

WHERE array  →  Piql parser  →  parameterized SQL + binds
     ↑
table('products')->filter([...])  |  ->where([...])  |  Agg / Where builders

Porm passes WHERE arrays to Piql, which implements a Medoo-compatible DSL. Use them in where(), filter(), all($where), get($where), aggregates, and Agg / Where builders.

Equality (default)#

['status' => 'open', 'project_id' => 1]
// status = 'open' AND project_id = 1

Comparison operators#

Prefix the column with the operator:

table('products')->filter(['priority[>]' => 2, 'sort_order[<=]' => 100])->all();

LIKE#

IN / NOT IN#

['id' => [1, 2, 3]]           // IN (1,2,3)
['status[!]' => ['archived']]  // NOT IN

NULL#

['completed_at' => null]   // IS NULL
['assignee_id[!]' => null]    // IS NOT NULL

AND / OR groups#

[
    'AND' => [
        'status' => 'open',
        'OR' => [
            'priority' => 1,
            'priority' => 2,
        ],
    ],
]

Top-level keys in a flat array are AND-ed. Use explicit AND / OR keys for nesting.

ORDER, LIMIT, GROUP, HAVING#

On a Builder chain, prefer fluent methods. In raw WHERE arrays:

FULLTEXT — match()#

On the Builder:

table('products')
    ->filter()
    ->match('title,description', 'pionia-shop sprint', 'natural')
    ->all();

Adds a MATCH … AGAINST style clause (driver-dependent).

Column aliases & casts#

Select with alias: 'column(alias)' in columns().

Cast on read: suffix [Int], [Bool], [Number], [String], [JSON], [Object]:

table('products')->columns(['metadata[JSON]'])->get(1);

Programmatic builders#

Where builder#

use Pionia\Porm\Database\Builders\Where;

$clause = Where::builder()
    ->and(['priority[>]' => 2])
    ->or(['status' => 'blocked'])
    ->build();

table('products')->filter($clause)->all();

Agg builder#

For HAVING-style expressions, comparisons in WHERE, and computed columns — see Aggregation.

Raw fragments#

use Pionia\Porm\Core\Piql;

table('products')->filter([
    'created_at[>]' => Piql::raw('DATE_SUB(NOW(), INTERVAL 7 DAY)'),
])->all();

See also Transactions & raw SQL.

Common mistakes#

• Passing user search text into Piql::raw() — use bound where() or [~] LIKE operators for Pionia Shop search boxes.
• Duplicate keys inside OR arrays — PHP keeps only the last value; nest groups or use whereIn().
• Using [Object] casts on untrusted columns — never on client-supplied JSON in Pionia Shop staging data.
• Embedding ORDER/LIMIT in WHERE when a Builder exists — prefer orderBy() / limit() on filter() chains in services.

What’s next#