Derafu Query

# An expression is a path and a filter, separated by "?". It is plain text, so
# it fits in a URL (encode a whole expression as one value).
#
#   customers __ invoices[on:id=customer_id] __ total ? >1000
#   first table   join and its condition       column   filter
#
# The path:
#   segments are separated by "__": the first is the table, the middle ones are
#   joined, the last one is the column.
#   [on:id=customer_id]  the join condition: "id" of the previous table is
#                        "customer_id" of this one
#   [alias:c]            an alias for the table
#   [join:left]          the type of join: inner (the default), left or right
#
# The filter: an operator and a value, such as ">1000", "in:a,b,c" or "is:null".

$qb->where('customers__invoices[on:id=customer_id]__total?>1000');

# The builder writes:
#   INNER JOIN invoices ON customers.id = invoices.customer_id
#   WHERE invoices.total > :param      (1000 is a bound parameter)

price?=1000                  price = :p
status?!=archived            status != :p
total?>=500                  total >= :p

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

status?in:paid,issued        a list of values
created_at?date:20240101     a date
deleted_at?is:null           no value at all

# ^ starts with, $ ends with, ~~ contains, and the ones that end with * ignore the case.
# The builder writes ILIKE, or the equivalent, for each database.
# The catalogue also has ranges, regular expressions and bitwise operators,
# and you can use your own catalogue.

# && is AND, || is OR, and parentheses group, as in any language.

status?=active&&total?>1000
    status = :p1 AND total > :p2

category?=electronics||category?=hardware
    category = :p1 OR category = :p2

status?=active&&(type?=person||tax_id?^78)
    status = :p1 AND (type = :p2 OR tax_id LIKE :p3)

# && binds tighter than ||.

# Or pass a list: each element is added with AND. Handy when the filters are dynamic.
$qb->where(['status?=active', 'total?>1000']);

# A path that starts with "___" is a subquery instead of a join.
# It filters by the children of a row without repeating the row.

# Invoices that have a pending payment:
___payments[on:id=invoice_id]__status?=pending
    EXISTS (SELECT 1 FROM payments
            WHERE invoices.id = payments.invoice_id AND payments.status = :p)

# Invoices with no payments at all:
___payments[on:id=invoice_id]?is:empty
    NOT EXISTS (SELECT 1 FROM payments WHERE ...)

# Aggregates: SUM, AVG, COUNT, MIN and MAX.
___payments[on:id=invoice_id]__SUM(amount)?>=1200
    (SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id = invoices.id) >= :p

// With your own builder: you keep it, and Derafu Query adds the conditions.
// The same two lines work with Doctrine DBAL, Doctrine ORM and Eloquent.
$condition = $parser->parse('status?=active&&total?>1000');

$applier = new IlluminateQueryBuilderConditionApplier();
$builder = Invoice::query();
$applier->apply($builder, $condition);
$invoices = $builder->get();

// With the builder of the package, from a configuration in an array, YAML or JSON:
$config = new QueryConfig([
    'table'   => 'products',
    'select'  => 'id, name, price',
    'where'   => 'category?=electronics',
    'orderBy' => ['price' => 'DESC'],
    'limit'   => 10,
]);
$result = $config->applyTo($queryBuilder)->execute();