Skip to content

Query Engine

Belikhun edited this page Sep 15, 2026 · 1 revision

🔍 Query engine

A fluent builder over plain tables and models, covering selects, joins, grouping, aggregates, subqueries and updates through one API. The same SQL is compiled for MySQL and SQLite, with the differences between the two handled by the engine.

Starting a query

use Blink\DB;

DB::table("orders");
DB::table([ "orders", "o" ]);          // aliased
Order::query();                         // from a model
DB::table([ $subQuery, "recent" ]);    // from a subquery, alias required

Building

$query = DB::table([ "orders", "o" ])
	-> select("o.id", [ "c.name", "customer" ])
	-> join([ "customers", "c" ], "c.id", "o.customer_id")
	-> leftJoin([ "payments", "p" ], "p.order_id", "o.id")
	-> where("o.status", "paid")
	-> where("o.total", ">=", 100)
	-> whereNot("o.channel", [ "test", "internal" ])
	-> whereOr("o.flagged", true)
	-> search("acme 2024", [ "c.name", "o.reference" ])
	-> distinct()
	-> group("o.id")
	-> sort("o.created", "DESC")
	-> limit(from: 0, count: 20);

Conditions

where(column, value) compares for equality, or IN for an array. where(column, operator, value) takes =, <>, >, <, >=, <=, LIKE. whereNot, whereOr and whereOrNot negate or join with OR. A closure groups conditions:

use Blink\Query\QueryBuilder;

$query -> where(function (QueryBuilder $group) {
	$group -> where("o.status", "paid")
		-> whereOr("o.status", "shipped");
});

For a model, Model::col("property") names a column by its property name and Model::col("#") its primary key:

Order::query()
	-> where(Order::col("#"), [ 1, 2, 3 ])
	-> where(Order::col("status"), Order::STATUS_PAID);

LIKE patterns use ! as the escape character on both engines; Expr::likePattern($text) builds one safely from user input.

Joins

$query -> join([ "items", "i" ], "i.order_id", "o.id");

$query -> join([ "items", "i" ], function (QueryBuilder $on) {
	$on -> where("i.order_id", "o.id")
		-> where("i.quantity", ">", 0);
});

Order::query() -> join(Item::t(), Order::col("#"), Item::col("order"));

Search

search($text, $columns) splits the text into words and requires every word to appear in at least one of the columns. Inline filters in the text (status=paid, total>=100) become conditions on those columns. On MySQL, fulltext: true uses MATCH ... AGAINST and needs a fulltext index on the columns.

Reading

$query -> all();                      // object[]
$query -> all(0, 20, idIndexed: true); // keyed by id
$query -> first();                    // ?object
$query -> exists();                   // bool
$query -> count();                    // int
$query -> sum("o.total");             // number
$query -> value("o.total");           // first row's column
$query -> values("o.id");             // one column of every row
$query -> ids();                      // primary keys
$query -> iterator();                 // row by row

foreach ($query -> iterator() as $order) {}

A model query returns model instances from all(), first() and iterator().

firstOrCreate() returns the first match or inserts the given fill:

DB::table("settings") -> where("name", "theme") -> firstOrCreate(array( "name" => "theme", "value" => "dark" ));

Writing

DB::table("orders")
	-> where("id", $id)
	-> set("status", "shipped")
	-> set("shipped_at", time())
	-> update();

DB::table("orders") -> where("id", $id) -> increase("views");
DB::table("orders") -> where("id", $id) -> decrease("stock", amount: 3);
DB::table("orders") -> where("status", "draft") -> where("created", "<", $cutoff) -> delete();
DB::table("sessions") -> truncate();

Straight to the table, without a builder:

DB::insert("orders", array( "reference" => "A-1", "total" => 120 ));   // returns the id
DB::inserts("items", $rows);                                            // one statement, returns the count
DB::update("orders", array( "id" => 7, "status" => "paid" ));
DB::remove("orders", array( "status" => "draft" ));
DB::exec("UPDATE orders SET total = total * ? WHERE currency = ?", array( 1.1, "EUR" ));

Transactions

DB::transaction(function () use ($order) {
	$order -> status = "paid";
	$order -> save();
	Payment::create(array( "order" => $order -> id, "amount" => $order -> total )) -> save();
});

Nested calls share the outer transaction; a throw anywhere rolls the whole thing back and rethrows.

Expressions

Blink\Query\Expression\Expr builds the pieces a plain string cannot:

use Blink\Query\Expression\Expr;

DB::table([ "orders", "o" ])
	-> select(
		[ Expr::count("o.id"), "orders" ],
		[ Expr::sum("o.total"), "revenue" ],
		[ Expr::max("o.created"), "latest" ],
		[ Expr::concatWs(" ", "c.first_name", "c.last_name"), "customer" ]
	)
	-> where(Expr::lower("o.channel"), "web")
	-> where(Expr::isNotNull("o.paid_at"))
	-> group("o.customer_id")
	-> all();

Available: count, sum, min, max, add, sub, mul, div, bitAnd, bitOr, bitXor, lower, upper, trim, concatWs, groupConcat, isNull, isNotNull, case(), caseValue(), and Expr::value() for a literal that must be sent as a parameter.

Expr::case()
	-> when("o.total", ">=", 1000) -> then("large")
	-> when("o.total", ">=", 100) -> then("medium")
	-> else("small");

Expr::caseValue("o.status")
	-> when("paid", 1)
	-> when("refunded", -1)
	-> else(0);

Subqueries

Anywhere a table goes, an aliased query goes too:

$latest = DB::table([ "payments", "p" ])
	-> select([ "p.order_id", "oid" ], [ Expr::max("p.created"), "paid_at" ])
	-> group("p.order_id");

DB::table("orders")
	-> join([ $latest, "lp" ], "lp.oid", "orders.id")
	-> all();

DB::table("orders")
	-> where("orders.total", ">", DB::table("orders") -> select(Expr::max("total")))
	-> all();

Middleware

Every terminal operation runs through Ether's query stage. A middleware sees the builder before it compiles and the SQL, parameters and result after; it can add a condition every query must carry, record timings or rewrite the operation. Per query:

DB::table("orders") -> middleware("tenant") -> withoutMiddleware("trace.query") -> all();

See Ether for writing one.

Two engines

The engine compiles for MySQL and SQLite from the same builder, and metric rows name the table and command either way. Where the two differ, the engine chooses for you: CONCAT_WS versus || with COALESCE, LIKE ... ESCAPE '!' on both, DELETE FROM in place of TRUNCATE on SQLite, fulltext search on MySQL only.

DB::family() returns "mysql" or "sqlite" for the rare case a query has to know.

Metrics

Every statement is recorded as a QueryMetric: the SQL with comments stripped and IN (?, ?, ?) lists collapsed to IN (?**), its hash, command, table, row count, duration and whether it raised. The error page's Queries context lists them, and a failed statement is kept and marked rather than dropped.

Clone this wiki locally