Query Builder

derafu/query provides two complementary ways to build queries:

1. Fluent API (SqlQueryBuilder) — chainable method calls, useful in application code.
2. Declarative Config (QueryConfig) — array, YAML, or JSON definitions, useful for reusable templates and API-driven queries.

Both produce the same output: a SqlQuery with an SQL string and named-parameter array.

Setting Up SqlQueryBuilder

SqlQueryBuilder requires an engine and an expression parser:

use Derafu\Query\Builder\SqlQueryBuilder;
use Derafu\Query\Engine\PdoEngine;
use Derafu\Query\Filter\CompositeExpressionParser;
use Derafu\Query\Filter\ExpressionParser;
use Derafu\Query\Filter\FilterParser;
use Derafu\Query\Filter\PathParser;
use Derafu\Query\Operator\OperatorLoader;
use Derafu\Query\Operator\OperatorManager;

// 1. Set up the operator registry.
$loader  = new OperatorLoader();
$manager = new OperatorManager(
    $loader->loadFromFile('vendor/derafu/query/resources/operators.yaml')
);

// 2. Build the expression parser.
$parser = new CompositeExpressionParser(
    new ExpressionParser(new PathParser(), new FilterParser($manager))
);

// 3. Create a database engine.
$pdo    = new PDO('pgsql:host=localhost;dbname=mydb', $user, $pass);
$engine = new PdoEngine($pdo);

// 4. Instantiate the builder.
$qb = new SqlQueryBuilder($engine, $parser);

For Doctrine DBAL use DoctrineEngine instead of PdoEngine. For framework-integrated setups, consider the bridges instead of SqlQueryBuilder.

Fluent API Reference

All mutating methods return $this (or a clone via new()) so they can be chained.

table(string $table, ?string $alias = null): self

Clones the builder through new() and changes the clone’s FROM table and alias. It does not clear existing filters, selected columns, joins, grouping, ordering or pagination.

Retain the returned builder when you want to modify that clone. Start from a fresh SqlQueryBuilder instance when you need an independent query without inherited state.

$qb->table('products')->where('price?>1000')->execute();
$qb->table('invoices', 'i')->select('i.id, i.total')->execute();

from(string $table, ?string $alias = null): self

Sets the FROM clause without resetting. Use table() for new queries; use from() internally or when you already have a fresh builder.

select(string|array $columns, bool $sanitize = true): self

Sets the SELECT columns. Accepts a comma-separated string or an array.

$qb->select('id, name, price');
$qb->select(['id', 'name', 'price']);
$qb->select('c.name AS customer_name, i.total');
$qb->select('COUNT(*) AS total');
$qb->select('type')->distinct();

Pass $sanitize = false to skip identifier sanitization (use only for trusted, pre-validated expressions).

distinct(bool $distinct = true): self

Adds DISTINCT to the SELECT clause.

$qb->table('customers')->select('type')->distinct()->execute();
// → SELECT DISTINCT type FROM customers

where(string|array|Condition|CompositeCondition $condition): self

Sets the WHERE clause. Resets any previous WHERE conditions. All elements are AND-combined.

$qb->where('status?=active');
$qb->where(['status?=active', 'total?>1000']);
$qb->where('status?=active&&total?>1000');  // composite string

andWhere(string|array|Condition|CompositeCondition $condition): self

Appends conditions to the existing WHERE with AND. If no WHERE exists yet, behaves like where().

$qb->where('status?=active')->andWhere('total?>1000');

orWhere(string|array|Condition|CompositeCondition $condition): self

Wraps the existing WHERE and the new condition in an OR composite.

// WHERE (category = 'electronics' OR category = 'hardware')
$qb->where('category?=electronics')->orWhere('category?=hardware');

// WHERE (category = 'software' OR (category = 'hardware' AND price > 200))
$qb->where('category?=software')->orWhere(['category?=hardware', 'price?>200']);

// Multiple OR groups:
// WHERE (status = 'cancelled' OR status = 'draft' OR (status = 'issued' AND total > 1000))
$qb->where('status?=cancelled')
   ->orWhere(['status?=draft', ['status?=issued', 'total?>1000']]);

andWhereOr(array|Condition|CompositeCondition $conditions): self

Appends an OR composite to the existing AND WHERE. Each element of the array is one OR branch; arrays within the array become AND groups.

// WHERE status = 'active' AND (type = 'person' OR tax_id LIKE '78%')
$qb->where('status?=active')
   ->andWhereOr(['type?=person', 'tax_id?^78']);

// WHERE customer_id IN (1, 2) AND ((status = 'paid' AND total > 1000) OR (status = 'issued' AND date >= '2024-03-01'))
$qb->where('customer_id?in:1,2')
   ->andWhereOr([
       ['status?=paid', 'total?>1000'],
       ['status?=issued', 'date?>=2024-03-01'],
   ]);

join(string $table, string $condition, string $type = 'INNER', ?string $alias = null): self

Adds an explicit JOIN clause. Valid types: INNER, LEFT, RIGHT, CROSS. Duplicates (same table+alias combination) are ignored.

$qb->join('customers', 'i.customer_id = c.id', 'INNER', 'c');

Convenience wrappers:

$qb->innerJoin('customers', 'i.customer_id = c.id', 'c');
$qb->leftJoin('payments', 'i.id = p.invoice_id', 'p');
$qb->rightJoin('invoices', 'c.id = i.customer_id', 'i');
$qb->crossJoin('currencies');

Note: When using path expressions in where(), joins are generated automatically from the path segments. Manual join() calls are only needed when you want explicit control, or when building the query without path expressions.

groupBy(string|array $columns): self

Adds GROUP BY columns. Multiple calls accumulate.

$qb->groupBy('category');
$qb->groupBy(['c.id', 'c.name']);

having(string|array|Condition|CompositeCondition $condition): self

Sets the HAVING clause. Accepts the same expression formats as where().

$qb->groupBy('category')->having('AVG(price)?>500');
$qb->groupBy('status')->having('COUNT(*)?>1');

orderBy(string|array $columns, string $direction = 'ASC'): self

Sets ORDER BY. Accepts a column string, or an associative array of column => direction.

$qb->orderBy('price', 'DESC');
$qb->orderBy(['category' => 'ASC', 'price' => 'DESC']);
$qb->orderBy(['created_at', 'id']);  // defaults to ASC

limit(int $limit): self / offset(int $offset): self

Set pagination. OFFSET is only appended when both LIMIT and OFFSET are set.

$qb->limit(20)->offset(40);
// → LIMIT 20 OFFSET 40

getQuery(): QueryInterface

Builds and returns the SQL without executing. Returns a SqlQuery that implements both QueryInterface and ArrayAccess:

$query = $qb->table('products')->where('price?>1000')->getQuery();

echo $query['sql'];         // SELECT * FROM products WHERE (price > :param_price_...)
var_export($query['parameters']);  // ['param_price_...' => '1000']

execute(): array

Builds and executes the query, returning rows as an array of associative arrays.

$rows = $qb->table('products')->where('price?>1000')->execute();

Complete Query Examples

Simple Filters

// Active customers.
$qb->table('customers')->where('status?=active')->execute();

// Products in a price range.
$qb->table('products')->where('price?between:100,1000')->execute();

// Soft-deleted records.
$qb->table('users')->where('deleted_at?isnot:null')->execute();

// March 2024 invoices (SQLite).
$qb->table('invoices')->where('date?period:202403')->execute();

Composite Conditions

// AND: software products over $200.
$qb->table('products')
   ->where(['category?=software', 'price?>200'])
   ->execute();

// OR: electronics or hardware.
$qb->table('products')
   ->where('category?=electronics')
   ->orWhere('category?=hardware')
   ->execute();

// AND + OR: active customers who are persons OR whose tax_id starts with "78".
$qb->table('customers')
   ->where('status?=active')
   ->andWhereOr(['type?=person', 'tax_id?^78'])
   ->execute();

Path-Based Joins

// Auto-generated JOIN from path.
$qb->select('c.name AS customer_name, i.number, i.total')
   ->where('customers[alias:c]__invoices[on:id=customer_id,alias:i]__total?>1000')
   ->execute();
// → SELECT … FROM customers AS c INNER JOIN invoices AS i ON c.id = i.customer_id WHERE i.total > :p

// Multi-level join.
$qb->select('p.name, i.number, c.name AS customer')
   ->where([
       'products[alias:p]__category?=electronics',
       'products[alias:p]__invoice_details[on:id=product_id,alias:id]__invoices[on:invoice_id=id,alias:i]__status?=paid',
       'products[alias:p]__invoice_details[on:id=product_id,alias:id]__invoices[on:invoice_id=id,alias:i]__customers[on:customer_id=id,alias:c]__type?=company',
   ])
   ->execute();

Subquery / EXISTS

// Invoices with no payments.
$qb->table('invoices')
   ->where('___payments[on:id=invoice_id]?is:empty')
   ->execute();
// → SELECT * FROM invoices WHERE NOT EXISTS (SELECT 1 FROM payments WHERE invoices.id = payments.invoice_id)

// Invoices with at least one pending payment.
$qb->table('invoices')
   ->where('___payments[on:id=invoice_id]__status?=pending')
   ->execute();

// Invoices where total payments >= 1200.
$qb->table('invoices')
   ->where('___payments[on:id=invoice_id]__SUM(amount)?>=1200')
   ->execute();

Grouping, Having, Ordering, Pagination

// Category stats.
$qb->table('products')
   ->select('category, AVG(price) AS avg_price')
   ->groupBy('category')
   ->having('AVG(price)?>500')
   ->orderBy('avg_price', 'DESC')
   ->limit(10)
   ->execute();

// Paginated customer list.
$qb->table('customers')
   ->orderBy(['name' => 'ASC'])
   ->limit(25)
   ->offset(50)
   ->execute();

Explicit Joins

// Manual JOIN for a complex ON clause.
$qb->table('invoices', 'i')
   ->select('i.id, i.number, i.total, c.name AS customer_name')
   ->innerJoin('customers', 'i.customer_id = c.id', 'c')
   ->where('i.status?=paid')
   ->execute();

// LEFT JOIN with GROUP BY.
$qb->table('customers', 'c')
   ->select('c.name, COUNT(i.id) AS invoice_count')
   ->leftJoin('invoices', 'c.id = i.customer_id', 'i')
   ->groupBy(['c.id', 'c.name'])
   ->execute();

Declarative Query Configuration

QueryConfig provides an array-based way to describe a query. It is particularly useful for storing query templates in files and for API-driven queries.

Basic Usage

use Derafu\Query\Config\QueryConfig;

$config = new QueryConfig([
    'table'   => 'products',
    'select'  => 'id, name, price',
    'where'   => 'category?=electronics',
    'orderBy' => ['price' => 'DESC'],
    'limit'   => 10,
]);

$result = $config->applyTo($qb)->execute();

applyTo() calls the corresponding builder methods in order and returns the modified builder, so you can chain additional calls after:

$builder = $config->applyTo($qb);
if ($extraFilter) {
    $builder->andWhere('status?=active');
}
$rows = $builder->execute();

Loading from Files

// YAML file.
$config = QueryConfig::fromYamlFile('/path/to/queries/product_report.yaml');

// JSON file.
$config = QueryConfig::fromJsonFile('/path/to/queries/sales.json');

// Auto-detect by extension (.yml, .yaml, .json).
$config = QueryConfig::fromFile('/path/to/query.yaml');

// From strings.
$config = QueryConfig::fromYamlString($yamlString);
$config = QueryConfig::fromJsonString($jsonString);

Configuration Validation

QueryConfig validates its known options in the constructor, before applyTo() changes a builder:

• The configuration cannot be empty. table is optional when it can be inferred from filter paths.
• table and alias accept strings; distinct accepts a boolean; limit and offset accept integers. String numbers and other mismatched types are not coerced.
• select, groupBy and orderBy accept a string or an array of strings. Lists and keyed arrays retain their keys and values.
• where, andWhere, orWhere and having accept strings, condition/composite objects or nested arrays containing those values.
• andWhereOr accepts an array or a condition/composite object, not a standalone string.
• Each configured JOIN must be a map with string keys, containing a string table and an optional string alias. Non-CROSS JOINs also require a string condition. Positional lists and numeric field names are rejected. Additional named fields are preserved, and their values may contain arrays with numeric keys.
• Absent and null options are not applied. Null elements inside a condition/column array are invalid.
• Unknown options are retained but not applied. This is not an allowlist for client-controlled query capabilities.

The JSON/YAML loaders require an array with string root keys. Numeric root keys are rejected; numeric keys inside nested lists remain valid. The loaders can return an empty array, but constructing QueryConfig from it is rejected.

YAML Template Example

# recent_products.yaml
table: products
select: id, name, price, created_at
where: deleted_at?is:null
orderBy:
    created_at: DESC
limit: 20

$config  = QueryConfig::fromYamlFile('queries/recent_products.yaml');
$builder = $config->applyTo($qb);

// Add extra filter at runtime.
if ($category) {
    $builder->andWhere('category?=' . $category);
}

$rows = $builder->execute();

Complete Configuration Reference

All QueryConfig keys and their equivalent builder calls:

$config = new QueryConfig([
    'table'    => 'invoices',
    'alias'    => 'i',
    'select'   => 'i.id, i.number, c.name AS customer_name',
    'distinct' => true,

    'where'      => 'i.status?=paid',
    'andWhere'   => 'i.total?>1000',
    'orWhere'    => 'i.date?period:202403',
    'andWhereOr' => [
        ['i.category?=service', 'i.total?>500'],
        ['i.category?=product', 'i.total?>1000'],
    ],

    'innerJoin' => ['table' => 'customers', 'alias' => 'c', 'condition' => 'i.customer_id = c.id'],

    'groupBy' => ['i.status'],
    'having'  => 'COUNT(*)?>1',
    'orderBy' => ['i.created_at' => 'DESC'],
    'limit'   => 20,
    'offset'  => 40,
]);

API-Driven Queries

QueryConfig is well-suited for accepting query parameters from an API:

$requestData = $request->getJsonBody();

$config = new QueryConfig([
    'table'   => 'products',
    'where'   => $requestData['filters'] ?? [],
    'orderBy' => $requestData['sort'] ?? ['id' => 'ASC'],
    'limit'   => min($requestData['limit'] ?? 20, 100),
    'offset'  => max($requestData['offset'] ?? 0, 0),
]);

$rows = $config->applyTo($qb)->execute();

Validate every client-controlled filter against the fields, relationships, functions and operators permitted for that endpoint. Full path syntax validates structure; it does not grant access to a column or relationship. Apply an equivalent allowlist to client-controlled sort fields, and validate pagination types before applying limits. Keep tenant/ownership restrictions in server-controlled conditions or scopes.