Getting Started

Minimal Setup

Version note: 7.0.0-beta2 is the current line; read it together with the beta1 notes, which cover everything since 3.x. There is no 4.x/5.x/6.x — the jump to 7.x aligns major versions across the Aura family. docs/upgrading.md walks the breaking changes as ordered steps. If you are on 3.x, budget a real upgrade task, not a composer update. If you are already on 7.0.0-beta1, beta2 still has one migration item for you: ? placeholder values moved from numeric keys to generated names (see Gotchas §1).

Requirements & Installation — PHP 8.4 minimum (CI covers 8.4 and 8.5):
```
composer require aura/sqlquery:^7.0@beta
```
If your app or CI still runs 8.3, pin the constraint before anything else — the package will not install, and the failure surfaces at composer update time, not at runtime.

Register the factory with a dialect. The factory is constructed with a database name; the dialect decides quoting rules and which features exist (returning() is Postgres-only, onDuplicateKeyUpdate() MySQL-only):

// AppServiceProvider::register()
$this->app->singleton(\Aura\SqlQuery\QueryFactory::class, function ($app) {
    $driver = $app['db']->connection()->getDriverName(); // mysql, pgsql, sqlite, sqlsrv

    return new \Aura\SqlQuery\QueryFactory($driver);
});

First query — use named placeholders. They are self-documenting, they survive conditional composition, and they are the only form whose keys you control:

use Aura\SqlQuery\QueryFactory;

$factory = app(QueryFactory::class);

$select = $factory->newSelect()
    ->cols(['id', 'name'])
    ->from('users')
    ->where('active = :active', ['active' => true]);

$select->getStatement();  // SELECT "id", "name" FROM "users" WHERE active = :active
$select->getBindValues(); // [':active' => true]

Execute through Laravel's connection (Laravel passes named binds straight to PDO):
```
$rows = DB::select($select->getStatement(), $select->getBindValues());
```

First Use Case: Paginated, Filterable User List

$factory = app(QueryFactory::class);

$select = $factory->newSelect()
    ->cols(['u.id', 'u.name', 'u.email'])
    ->from('users u')
    ->where('u.active = :active', ['active' => true])
    ->orderBy(['u.name'])
    ->limit(25)
    ->offset(50);

if ($term = $request->string('q')->toString()) {
    $select->where('u.name LIKE :term', ['term' => "%{$term}%"]);
}

$users = DB::select($select->getStatement(), $select->getBindValues());

Note the limit(25)->offset(50) — both take ints under the 7.x native signatures. See Gotchas: passing the raw request string is the single most common 7.x breakage. And note :term, not ?: mixing the two in one statement is exactly what beta2 set out to eliminate.

Implementation Patterns

1. Query Composition

Fluent chaining. Every @return $this method now declares static, so chaining is type-safe and IDE-completable end to end:

$select = $factory->newSelect()
    ->cols(['id', 'name'])
    ->from('users')
    ->where('created_at > :since', ['since' => now()->subDays(7)])
    ->orderBy(['name']);

Sub-selects — the sub-query carries its own binds, and the parent clause claims them:

$recent = $factory->newSelect()
    ->cols(['user_id'])
    ->from('orders')
    ->where('created_at > :since', ['since' => now()->subMonth()]);

$select = $factory->newSelect()
    ->cols(['u.id', 'u.name'])
    ->from('users u')
    ->where('u.id IN (?)', [$recent]);   // sub-select binds survive

DB::select($select->getStatement(), $select->getBindValues());

CTEs (WITH) — the WithInterface methods (with(), withRecursive(), resetWith(), hasWith()) are on every query type and are the cleanest way to express recursive org trees or pre-aggregated sets:

$tree = $factory->newSelect()
    ->cols(['id', 'parent_id', 'name'])
    ->from('categories')
    ->where('parent_id IS NULL')
    ->unionAll(
        $factory->newSelect()
            ->cols(['c.id', 'c.parent_id', 'c.name'])
            ->from('categories c')
            ->join('tree t', 'c.parent_id = t.id')
    );

$select = $factory->newSelect()
    ->withRecursive('tree', $tree)
    ->cols(['*'])
    ->from('tree');

DB::select($select->getStatement(), $select->getBindValues());

Binds rendered inside a still-rendered UNION branch stay alive even after resetWhere()/resetUnions() on the outer query — beta2 made this explicit for generated ? names.

Unions — union()/unionAll() accept an optional ?SelectInterface argument, so you can either pass a built select or append clauses to the current one.

2. Dialect-Specific Features

Feature availability follows the QueryFactory dialect — get the constructor argument right or the methods simply won't exist.

MySQL upsert:

$insert = $factory->newInsert()
    ->into('users')
    ->cols(['email', 'name'])
    ->rows([
        ['[email protected]', 'John'],
        ['[email protected]', 'Jane'],
    ])
    ->onDuplicateKeyUpdate(['name' => 'updated_name']);

Postgres RETURNING:

$insert = $factory->newInsert()
    ->into('users')
    ->cols(['email', 'name'])
    ->rows([['[email protected]', 'John']])
    ->returning(['id']);

Postgres/SQLite conflict targets — Pgsql\Insert and Sqlite\Insert implement Common\OnConflictUpdateInterface; type-hint that interface (not the concrete class) if you write helper code that must work on both.

Postgres dollar-quoting — beta2 adds a Pgsql\Quoter that leaves $$...$$ / $tag$...$tag$ strings untouched. Before it, where('a = $$t.c$$') was rewritten to compare against "t"."c" (fixes #274). If you embed dollar-quoted strings, this is a reason to take the upgrade.

3. Integration with Eloquent / Laravel

Prefer passing the compiled SQL + binds to Laravel's query layer rather than hand-rolling PDO:

$sql    = $select->getStatement();
$binds  = $select->getBindValues();

// Raw read
$rows = DB::select($sql, $binds);

// As a constraint inside an Eloquent builder
$ids = User::query()
    ->whereIn('id', fn ($q) => $q->select('user_id')->from('orders'))
    ->pluck('id');

Transactions wrap cleanly because nothing in the package holds a connection:

DB::transaction(function () use ($factory, $payload) {
    $insert = $factory->newInsert()
        ->into('orders')
        ->cols(array_keys($payload))
        ->bindValues($payload);          // build once

    DB::insert($insert->getStatement(), $insert->getBindValues());
});

Build the query once into a variable and reuse getStatement()/getBindValues() — see the gotcha about rebuilding.

4. Dynamic Query Building

Conditional clauses, applied in whatever order the request dictates:

$select = $factory->newSelect()
    ->cols(['id', 'name'])
    ->from('users');

if ($request->boolean('active')) {
    $select->where('active = :active', ['active' => true]);
}

if ($search = $request->string('search')->toString()) {
    $select->where('name LIKE :search', ['search' => "%{$search}%"]);
}

Reusable query logic — extract a builder function or (better) a small factory-backed repository method, and always cast at the request edge:

function buildUserSelect(QueryFactory $factory, bool $activeOnly = false): SelectInterface
{
    $select = $factory->newSelect()
        ->cols(['id', 'name', 'email'])
        ->from('users');

    if ($activeOnly) {
        $select->where('active = :active', ['active' => true]);
    }

    return $select;
}

$page = (int) $request->input('page', 1);
$size = (int) $request->input('per_page', 25);

$select->limit($size)->offset(($page - 1) * $size);

Gotchas and Tips

1. `?` Placeholders Now Bind Under Generated Names (beta2's headline break)

A value given for a ? is bound under a generated name, exactly as a list already was:

$select->where('id = ?', [5]);
$select->getStatement();  // "id = :__1__"
$select->getBindValues(); // ['__1__' => 5]   ← was [0 => 5]

Passing getBindValues() straight to execute()/DB::select() is unaffected. What breaks is anything that reached the value by number:

// ❌ no longer works — there is no numeric key
$binds = $select->getBindValues();
$binds[0] = 99;
$select->bindValue(0, 99);

// ✅ replace it by the generated name, or just use a named placeholder
$select->bindValue('__1__', 99);

Rules worth internalising:

Never mix ? and named placeholders in one statement. beta2 guarantees this can't happen by naming ? binds; before, plain PDO rejected the mix on MySQL/Postgres and pdo_sqlite silently ran the ? unbound.
One-for-one counts. Values given for ? must match the number of ?s or InvalidArgumentException is thrown — for any count mismatch, not just lists/sub-selects.
No values given → ? stays. A condition given no values, or only named ones, keeps its ? for you to bind by hand (3.x behaviour). You are then responsible for positional binding.

Closure conditions fill in reading order, under names taken before the closure runs:

$select->where(fn ($q) => $q->where('a = ?')->where('b = ?', [6]), [5]);
// a = :__1__ AND b = :__2__   (5 then 6)

?? is not a placeholder — it's PDO's escape for a literal ?. Neither is a ? inside a comment or string literal, and each dialect decides what a literal is: MySQL treats # as a comment start and -- only before whitespace, so a--? there is subtraction plus a real placeholder; Postgres treats E'...' escape strings and $$...$$ dollar-quoted strings as literals.
Resets drop generated values. resetWhere(), resetHaving(), resetTables(), resetUnions(), resetWith() discard the generated values their clause bound — a name nobody can rebind is otherwise a parameter PDO rejects at execute. Exception: a value a still-rendered UNION branch spells stays. Values you bound under your own name are kept, as before.
Subclassing caveat: if you extended Select and overrode the protected $text_pattern property to change scanning, move to getTextPattern() — the reading moved up to AbstractQuery so conditions are scanned with it, and the old property no longer exists, so the override would be silently ignored.

Because each ? now gets its own name, two ?s in separate where() calls no longer throw — that was a beta1-only rule, retired here.

2. Named Placeholder Collisions Still Throw

Distinct named placeholders claiming conflicting values across clauses raise Aura\SqlQuery\Exception\LogicException. The classic UPDATE footgun is unchanged:

// ❌ LogicException: cols() and where() both claim :status with different values
$update->table('orders')
    ->cols(['status' => 'shipped'])
    ->where('status = :status', ['status' => 'pending']);

// ✅ distinct names
$update->table('orders')
    ->cols(['status' => 'shipped'])
    ->where('status = :old_status', ['old_status' => 'pending']);

Collisions are counted per clause: WHERE and HAVING binding one name to different values is caught, as are sub-select binds and closure-condition binds (no longer merged unchecked).
resetWhere(), resetHaving(), resetTables() release the names their clause claimed; bound values themselves survive a reset.
Union branches may share a name if they bind the same value (same tenant id on both sides is legal); resetUnions() leaves the name with its clause.
bindValue()/bindValues() bypass the check entirely — hand binding may still overwrite.
fromSubSelect()/joinSubSelect() binds are held by the clause that renders them until resetTables(), so resetWhere() on the outer query no longer frees a name the sub-select is still binding.

3. Native Types, `strict_types`, and Your Caller

Every parameter and return now has a native declaration. Which file you call from decides the strictness, not this package (it declares no strict_types):

Without declare(strict_types=1) — historical coercion still applies: limit('5') → LIMIT 5. New failure mode: limit('abc') throws TypeError where the old internal cast silently produced 0 and dropped the LIMIT.
With declare(strict_types=1) — every call is strict: limit('5') throws, limit(5) required.

The classic real-world hit is a limit/offset read from the request and passed straight through. Cast at that edge — (int) $request->input('per_page') — and keep offset() consistent with limit() being an int.

4. Exceptions: New Namespace, Old Interface Preserved

Concrete exceptions live in Aura\SqlQuery\Exception (LogicException, BadMethodCallException, InvalidArgumentException), each extending its SPL counterpart and implementing the new Aura\SqlQuery\ExceptionInterface marker (extends \Throwable).

try {
    $select->getStatement();
} catch (\Aura\SqlQuery\ExceptionInterface $e) {   // recommended catch-all
    report($e);
}

Aura\SqlQuery\Exception is now a deprecated interface extending that marker — existing catch (Aura\SqlQuery\Exception $e) blocks still work until 8.x.
Since all SPL parents derive from \LogicException, catch (\LogicException $e) also catches everything — including the new InvalidArgumentException from a ?/value count mismatch.
Code that instantiated or subclassed Aura\SqlQuery\Exception must move to a concrete class.

5. Table Alias Conflicts

Duplicate aliases throw:

// ❌ throws
$select->from('users u')->join('orders o', 'u.id = o.user_id')->join('users u', 'o.user_id = u.id');

// ✅ distinct alias
$select->join('users u2', 'o.user_id = u2.id');

6. Missing Columns, Unbound Parameters, Dialect Mismatch

newSelect()->from('users') with no cols() throws — always declare columns explicitly (also better for payload size than SELECT *).
Unbound placeholders surface at execute() time, not at getStatement(); call getBindValues() and eyeball it while debugging. This includes the ? you deliberately left for hand binding.
new QueryFactory() defaults to MySQL quoting. Against Postgres you'll get double quotes and no returning(). Always pass the driver name explicitly.
getStatement() is abstract on AbstractQuery — only relevant if you extend that base directly; Select and AbstractDmlQuery already implement it (they write union branches and WITH above build()).

7. Extension Points (Subclass Signatures Changed)

If you ship custom query classes, override with matching signatures or the class fatals on load:

use Aura\SqlQuery\Common\Select;

class UserSelect extends Select
{
    public function activeOnly(): static   // `static`, not `self`/`$this`
    {
        $this->where('active = :active', ['active' => true]);
        return $this;
    }
}

166 methods declare static return — subclasses overriding any of them must declare static too.
Properties declare native types; a subclass redeclaring one must match. $builder is the one exception (narrowed only in a docblock, since PHP native types are invariant). INSERT/UPDATE/DELETE table and conflict targets are nullable; last-insert-id names and ON DUPLICATE KEY UPDATE values start as empty arrays, not null.
Select::$text_pattern is gone (beta2) — override getTextPattern() on AbstractQuery instead if you customised condition scanning.
Protected methods reshaped: subSelect() moved from Common\Select up to AbstractQuery and takes a $source argument; rebuildCondAndBindValues() takes a $clause argument.
Interfaces gained methods: all four query interfaces extend WithInterface; SelectInterface::reset() returns static (was void); QueryInterface declares resetBindValues(). If you implement any interface outside the package, implement these — or extend the concrete class / use Common\WithTrait.
Quoter: Quoter::quoteNamesIn() and replaceNamesIn() return string (docblock was wrong, not the code). getLastInsertIdName() is string|null. Quoter is abstract; extend the dialect-specific quoter and declare matching types, then pass it to the factory. Pgsql\Quoter now skips dollar-quoted strings.

8. Laravel-S-specific Quirks

Keep a single source of truth for the SQL: build once, then getStatement() + getBindValues(). Rebuilding the query for the second call is how bind arrays and SQL drift apart.
Wrap multi-statement work in DB::transaction(); the package holds no connection state, so it composes cleanly.
DB::select($sql, $binds) handles both named binds and the generated __N__ names directly — no manual PDO::prepare. You only need positional binding when you deliberately leave a ? unvalued.

9. Testing Tips

Queries are plain objects, so assertion is trivial:

$select = $factory->newSelect()->cols(['id'])->from('users');
$this->assertSame('SELECT "id" FROM "users"', $select->getStatement());
$this->assertSame([], $select->getBindValues());

Assert the exact rendered SQL including dialect quoting — it catches factory-dialect mistakes early and pins behavior when you later upgrade past 8.x.
If you use ? anywhere, assert the bind key shape too; ['__1__' => 5] is the beta2 contract, and a test asserting [0 => 5] is your early warning that some code still reaches by index:
```
$this->assertSame(['__1__' => 5], $select->getBindValues());
```
For feature tests, DB::transaction(fn () => ...) per test with rollback avoids snapshot juggling.

10. Performance

Always enumerate cols(); never SELECT *.
getStatement() is free to call for logging — use it in a DB::listen-style debug hook to see exactly what went to PDO, generated names included.
Sub-selects and CTEs are rendered inline (no extra round trips); the cost is entirely in your indexes.

Sqlquery Laravel Package