The Fluent Query Builder: Pushing Down to Native Indexes

Every PHP developer with a few years on them has lived the same afternoon: the ORM query that ran in forty milliseconds on your laptop takes nine seconds in production, and when you finally pull the generated SQL out of the query log and run EXPLAIN on it, the planner has decided your carefully placed index is not worth using today. The root cause is structural rather than incidental, because an ORM’s job is to hide the database behind an object graph, and a SQL planner’s job is to re-derive your intent from a string, and neither layer is accountable to the index decision you made at schema time. The MongrelDB PHP client takes the opposite bet: its fluent query builder does not generate SQL at all, and each condition you chain names the index family it wants directly, so the choice you made when you created the column is the one that executes on the server.

The condition is the index choice

The builder hangs off $db->query($table) and each where() call takes a condition type plus a parameter array; the type string is not sugar over a WHERE clause but the name of the engine structure that will answer it, with bitmap_eq and bitmap_in hitting the bitmap index on low-cardinality columns, range and range_f64 hitting the learned-range index, fm_contains hitting the FM-index for substring full-text, ann hitting the HNSW vector index, and sparse_match and min_hash_similar covering sparse vectors and set-similarity respectively.

use Visorcraft\MongrelDB\Database;

$db = new Database('http://127.0.0.1:8453');

$rows = $db->query('orders')
    ->where('bitmap_eq', ['column' => 2, 'value' => 'electronics'])
    ->where('range_f64', ['column' => 3, 'min' => 100.0, 'max' => 500.0])
    ->projection([1, 2, 3])
    ->limit(50)
    ->execute();

Conditions chain with AND semantics, and because each one is dispatched to its own index rather than compiled into a single SQL string, the composition you get is the intersection of two index lookups instead of a planner’s guess about selectivity order. The range family is split by column type on purpose: range takes int64 bounds (and covers timestamps, which are int64 underneath) and treats them as inclusive, while range_f64 is the float variant with lo_inclusive and hi_inclusive flags, so the example above uses range_f64 because the amount column is a Float64 and the server would otherwise be handed integer bounds for a float predicate. The full-text and vector cases read the same way, which is the part that would cost you a second service in most stacks:

// Substring full-text over the FM-index
$db->query('documents')
    ->where('fm_contains', ['column' => 2, 'pattern' => 'database performance'])
    ->limit(10)
    ->execute();

// Dense vector similarity over HNSW
$db->query('embeddings')
    ->where('ann', ['column' => 2, 'query' => [0.1, 0.2, 0.3], 'k' => 10])
    ->execute();

Friendly keys, canonical keys, one wire shape

The parameter arrays accept friendly aliases that the client translates before anything crosses the network, so column becomes column_id, min and max become lo and hi, and the server’s canonical spellings are accepted untouched if you prefer to write exactly what the daemon expects. You can see the whole translation by calling build() instead of execute(), which is also the fastest way to debug a query that is not returning what you expect, because the payload below is the literal JSON body of the POST to /kit/query:

$payload = $db->query('orders')
    ->where('range_f64', ['column' => 3, 'min' => 100.0])
    ->limit(50)
    ->build();

// ['table' => 'orders',
//  'conditions' => [['range_f64' => ['column_id' => 3, 'lo' => 100.0]]],
//  'limit' => 50]

One alias is deliberately not global: value maps to pattern only on fm_contains and fm_contains_all, because pk and bitmap_eq use value as their canonical key, and a blanket rename would silently corrupt exact-match conditions. That is the kind of edge a doc page would never mention and a unit test catches once, and it is worth knowing about before you copy a full-text example and adapt it to a primary-key lookup.

The honest signal: truncated()

A limit() on the builder is applied server-side, and when the engine has more matches than your limit it says so rather than letting you assume the result set is complete; execute() populates a flag you read back with truncated(), which turns “did I get everything?” from a superstition into a check, and offset() is the companion method that skips matching rows before the limit applies, which is what makes the two of them a usable pagination pair:

$query = $db->query('orders')
    ->where('range_f64', ['column' => 3, 'min' => 0.0])
    ->limit(100);

$rows = $query->execute();

if ($query->truncated()) {
    // The server capped this result; fetch the next page with
    // ->offset(100)->limit(100), or narrow the condition.
}

When you outgrow one retriever

The query builder answers “which rows match these conditions,” and when the real question is “which rows are most relevant across several signals at once,” the same client exposes $db->search($table), a separate builder for the /kit/search endpoint that runs multiple retrievers (dense ANN, sparse vectors, MinHash) in one request and fuses them with reciprocal-rank fusion, with must() for the same hard filter conditions the query builder uses and an optional exact-vector rerank on top. The two builders are deliberately separate entry points, because a filtered lookup and a ranked hybrid search are different operations with different latency profiles, and collapsing them into one method would hide a cost you want to see at the call site.

Why this is not an ORM, and why that is the point

The trade you make is real, so it is worth stating plainly: you address columns by the IDs the schema assigned them, you need to know which index exists on which column, and there is no lazy-loaded relation graph waiting to save you from a second query, because the builder is a thin, honest layer over the engine’s native condition protocol rather than an object-relational fiction on top of it. What you get back is that the code reads exactly like what the server does, the query that runs in production is the one you wrote rather than the one a planner re-derived, and when you genuinely want a planner there is always $db->sql() sitting next to the builder for the ad-hoc cases. PHP developers have spent two decades debugging the gap between what they asked an ORM for and what the database actually executed, and a builder that refuses to create that gap in the first place is the whole design in one sentence.