mouf/magic-query
Magic Query lets PHP developers describe SQL queries in JSON instead of hand-written strings, then compiles them into parameterized SQL with joins, unions and filters handled automatically. This reduces injection risk and makes complex queries easier to build, reuse and maintain across projects.
Install via Composer:
composer require mouf/magic-query
The package is a thin, dependency-free layer over PDO. There is nothing to configure at the framework level unless you are already using Mouf's DI container — you can wire it manually with a single constructor argument:
use Mouf\MagicQuery\MagicQuery;
$magic = new MagicQuery($pdo);
Where to look first: MagicQuery.php (the entry point — one main query() method), MagicQueryResult.php (the iterable result set), and MagicQueryResultRow.php (the per-row wrapper). The README is short and worth skimming end-to-end; it fits on one screen.
First use case — replace hand-rolled IN() expansion. The most common reason to reach for this package is that PDO cannot bind an array to a single placeholder. The classic workaround is implode(',', array_fill(0, count($ids), '?')), which is noisy and error-prone. With Magic Query the array is expanded automatically:
$result = $magic->query(
'SELECT id, email FROM users WHERE id IN (:ids) AND status = :status',
['ids' => [1, 2, 3], 'status' => 'active']
);
foreach ($result as $row) {
echo $row->email; // MagicQueryResultRow exposes columns as properties
// $row->toArray() // or as an array
}
The same call would have required three placeholders and manual parameter ordering before. Note that parameter order in the array does not matter — the library maps names, not positions.
Associative parameters, order-independent. Always use named placeholders. Because Magic Query re-parses the SQL and binds by name, passing an associative array is idiomatic and lets you build the parameter array incrementally (e.g., filters added conditionally) without tracking positional indexes.
Bulk inserts with arrays of arrays. If a placeholder sits alone inside a VALUES(...) tuple, passing an array of arrays produces multiple tuples:
$magic->query(
'INSERT INTO tags (article_id, label) VALUES (:rows)',
['rows' => [[1, 'php'], [1, 'sql'], [2, 'orm']]]
);
This is the second most valuable feature in the package — it eliminates the copy-pasted loop that builds the (?, ?), (?, ?), ... string.
Repository / service layer. Wrap MagicQuery in your own repository classes so the rest of the application never sees PDO. A typical pattern:
final class UserRepository
{
public function __construct(private MagicQuery $magic) {}
public function findActiveByIds(array $ids): iterable
{
if ($ids === []) {
return [];
}
return $this->magic->query(
'SELECT * FROM users WHERE id IN (:ids) AND active = :active',
['ids' => $ids, 'active' => true]
);
}
}
The empty-array guard is essentially mandatory — see Gotchas.
Custom parameter objects. The package exposes an interface for "smart" parameters (an object that knows how to render itself into SQL plus its bound values). This is the extension point for things like a StatusFilter value object, a DateRange, or a tenant scoping object. Implement it when you find yourself building the same fragment (AND created_at BETWEEN :from AND :to) in several places, and keep the raw-SQL surface as small as possible — an object that just echoes $_GET into the query is an injection waiting to happen.
Inspect before you run. Use the library's SQL/parameter introspection (the generated query is available before execution) for query logging, EXPLAIN, or for handing off to another DBAL layer. This is also the fastest debugging loop: dump the generated SQL and the bound parameters, paste into your SQL client, and iterate.
Result set semantics. MagicQueryResult is iterable but is fundamentally a cursor result, not a collection. If you need count(), array_map, or multiple passes, convert to an array immediately — do not assume the iterator can be rewound.
IN (:ids) with ['ids' => []] will either produce IN () (a syntax error) or a placeholder that matches nothing, depending on the version. Always short-circuit empty collections in the caller. This is the single most common production bug with this library.WHERE id IN (:ids) works; WHERE CONCAT(',', :ids, ',') LIKE ... or LIKE '%:name%' will not expand — the parser only treats a placeholder surrounded by SQL punctuation as a list. Keep array-bound placeholders adjacent to IN/VALUES/= and nothing else.:name syntax. Positional ? placeholders are not the intended input, and mixing the two is asking for trouble.:name appears twice, be explicit about whether you want the same value bound twice or a duplicated parameter array — behavior varies with PDO's ATTR_EMULATE_PREPARES setting. Test both code paths if you toggle emulation between CLI and web.max_allowed_packet limit. Chunk bulk inserts (array_chunk($rows, 500)) as a matter of habit.null, and integers passed inside an array may be bound as strings. If your driver is picky (PostgreSQL jsonb, strict int comparisons), cast in SQL or verify the generated types rather than trusting the array values.composer.json with a caret constraint and run the test suite after every minor bump.var_dump inside a foreach over MagicQueryResult — the query executes lazily against the cursor and you will see the error at iteration time, not at query() time.PDOException. Wrap the call in your repository if you want domain-level exceptions.where(), join(), or fluent API. The library solves exactly one problem — variable-arity prepared statements — and does it well. If you need composition beyond that, pair it with a builder or write explicit SQL; do not try to repurpose it as a full abstraction layer.How can I help you explore Laravel packages today?