Sqlquery Laravel Package

Product Decisions This Supports

• Runtime & Language Roadmap: 7.0.0-beta2 (and beta1 before it) requires PHP 8.4+ (CI matrix covers 8.4/8.5). This package is no longer a fit for legacy-PHP codebases—adopting it is an explicit commitment to the modern-PHP upgrade track, and should be sequenced after (or alongside) the platform's PHP 8.4 migration, not before. Two betas in, the 3.x → 7.x jump remains a single coordinated planning item across the Aura family.

• Database Abstraction Layer (DAL) Strategy: Still the core build vs. buy lever for query construction—reduces reliance on Eloquent or raw SQL strings and keeps business logic decoupled from database-specific syntax. MIT-licensed, dependency-light.

• Multi-Database Compatibility (strengthened): MySQL, PostgreSQL, SQLite, SQL Server via any PDO connection. Beta2 fixes a genuine portability trap: a statement that mixed ? and named placeholders is rejected outright by PDO on MySQL and PostgreSQL, and on pdo_sqlite ran with the ? silently unbound. After 7.0, ? values are bound under generated names (where('id = ?', [5]) renders id = :__1__), so a statement never mixes placeholder styles. Queries that "worked" on one driver and failed on another are normalized.

• Analytics & Reporting Roadmap: CTEs remain first-class. with(), withRecursive(), resetWith(), and hasWith() on all four query interfaces mean recursive hierarchies (org charts, category trees, BOMs) and multi-step reporting queries can be built in the query builder instead of escaping to raw SQL. This is still the biggest capability change since 3.x and directly de-risks "reporting feature requires hand-written SQL" scope.

• Data-Integrity / Correctness Posture (revised from beta1): Beta1 proposed throwing on all placeholder collisions; beta2 replaces that for anonymous ? placeholders—each is now bound under a generated name, so separate where() calls no longer collide at all. What still fails loudly:

  • Duplicate named placeholders across clauses (e.g. SET status = :status WHERE status = :status) → LogicException, not a silently wrong UPDATE.
  • ? value count mismatch → InvalidArgumentException, now for any count of values, not just lists or sub-selects.
  • Non-coercible arguments (limit('abc')) → throw instead of silently becoming LIMIT 0.
  • Unbound ? in a statement that would have been rejected at execute time.

  Net read for stakeholders: beta2 is more permissive than beta1 but strictly safer than 3.x. It trades "fail on collision" for "eliminate the collision by construction," which is the better outcome—fewer upgrade breaks, same class of bug removed.

• Error Handling & Observability: Aura\SqlQuery\Exception namespace (LogicException, BadMethodCallException, InvalidArgumentException), each extending its SPL counterpart and implementing ExceptionInterface (extends \Throwable) as the recommended catch-all. Since all SPL parents derive from \LogicException, catch (\LogicException $e) catches everything—useful for uniform error taxonomy and alerting.

• Developer Experience (DX): Full native parameter and return types (166 @return $this methods now declare static) give IDEs and static analysis (PHPStan/Psalm) real signatures instead of docblock promises. Faster onboarding, better refactor safety.

• Performance Optimization:

  • Bulk operations: addRows() for multi-row inserts (fewer round-trips).
  • Paging: setPaging() recalculates LIMIT/OFFSET dynamically.
  • Subquery binding: placeholders preserved in nested queries, with values tracked per clause rather than merged unchecked.

When to Consider This Package

• Consider if:
  • Analytics or hierarchical data is on the roadmap: native with() / withRecursive() replaces raw-SQL escape hatches for reporting and tree traversal.
  • You're standardizing on PHP 8.4+: versioning is aligned across the Aura family (3.x → 7.x, no 4/5/6), so a coordinated Aura upgrade is one planning item.
  • You run more than one database driver: the beta2 placeholder normalization removes a whole class of "passes on SQLite, fails on PostgreSQL" defects.
  • Legacy SQL cleanup: replacing spaghetti SQL strings with maintainable, now-typed OOP queries.
  • Multi-tenant SaaS: dynamic database switching without ORM bloat.
  • Correctness matters more than lenient behavior: you want duplicate named placeholders and unmatched value counts to fail loudly rather than silently degrade.
• Avoid / look elsewhere if:
  • PHP < 8.4 anywhere in the fleet: this is a hard floor. Stay on the 3.x line (support/security exposure to weigh) or defer.
  • ActiveRecord ORM needed: teams standardized on Eloquent will fight the model-centric vs. query-centric mismatch.
  • NoSQL / graph databases: SQL-only; use MongoDB PHP Library or Neo4j PHP Client.
  • Heavy subclassing of query classes or custom interface implementations: 7.x is the most invasive release to date for extenders—every overridden method needs a matching native signature (static where documented @return $this), every redeclared property needs a matching type, and out-of-package implementers of SelectInterface/InsertInterface/UpdateInterface/DeleteInterface must add the WithInterface methods and the new signatures (union(?SelectInterface $select), join(..., array $bind = []), reset() returning static, QueryInterface::resetBindValues()). Beta2 adds one more: $text_pattern on Select is gone, replaced by getTextPattern().
  • Real-time sync / ACID-heavy workloads: no built-in transaction management or event listeners; pair with Doctrine DBAL if ACID guarantees are the requirement.
• Migration gates before adopting 7.x:
  1. Casting at the request edge: with declare(strict_types=1) in a calling file, limit('5') now throws—a limit/offset read straight from a request is the classic break. Cast at the boundary. Without strict types, scalars still coerce; only non-coercible values now throw instead of silently dropping the LIMIT.
  2. Binding by name, not by index: where('id = ?', [5]) now yields getBindValues() === ['__1__' => 5] instead of [0 => 5]. Passing getBindValues() straight to execute() is unaffected, but anything that reads getBindValues()[0] or calls bindValue(0, ...) must be rewritten to use the generated name. This is the beta1 → beta2 delta and the main reason a spike branch that already started on beta1 must be re-checked.
  3. ? value arity: values given for ? must match the ? one for one (any mismatch, any count, throws InvalidArgumentException). Closure conditions inherit the outer ? values in reading order, under names taken before the closure runs—verify closure-heavy condition builders rather than assuming.
  4. $text_pattern overrides: a beta1-era subclass that overrode the protected Select::$text_pattern property must override getTextPattern() instead; the property no longer exists and an override of it is silently ignored.
  5. Reset semantics: resetWhere(), resetHaving(), resetTables(), resetUnions(), resetWith() drop the generated values their clause bound (for ? values and lists alike) unless a rendered UNION branch still spells them—nobody can rebind a generated name, and a leftover value is a parameter PDO rejects at execute. Values bound under a name you wrote are kept, as before.
  6. Exception handling: any code that instantiates or subclasses Aura\SqlQuery\Exception directly must move to a concrete class; catch (Aura\SqlQuery\Exception $e) keeps working but is deprecated until 8.x.
  7. Custom query types extending AbstractQuery must implement getStatement(); subclasses overriding subSelect() or rebuildCondAndBindValues() must match their new shapes.
  8. Pin deliberately: aura/sqlquery to ^7.0@beta. Two betas plus a cross-major jump still warrant a spike branch and a rollback plan—and beta2 changed behavior beta1 shipped, so don't assume a beta1 green suite means beta2 is green.

How to Pitch It (Stakeholders)

For Executives

"Aura.SqlQuery 7.0 lets us keep writing database queries in PHP without vendor lock-in—SQL's power without raw strings, MIT-licensed and battle-tested across four major databases. This release modernizes it to match where our platform is going.

• Unlocks reporting features we'd otherwise hand-build: native support for recursive queries (org charts, category trees, multi-step analytics) means those features land in the query builder instead of bespoke SQL.
• Prevents silent data corruption: the library now refuses to run a query where one value could be mistaken for another, or where the number of values doesn't match the statement—previously that scenario could quietly write the wrong value to a column, exactly the kind of incident that costs us trust and audit findings.
• Removes a driver-portability defect class: the new release guarantees a statement never mixes anonymous and named placeholders, a combination MySQL and PostgreSQL simply reject and SQLite ran incorrectly. Reporting queries that behaved differently per environment become predictable.
• Modern runtime alignment: requires PHP 8.4+, which is where our upgrade roadmap is headed anyway; adopting on 7.x keeps us on one coordinated version across the Aura packages.

Investment: a Composer bump plus a contained migration pass (updating query-builder subclasses, casting request inputs before they hit limit()/offset(), and renaming any code that addressed bind values by numeric index). ROI: fewer raw-SQL escape hatches, fewer data-integrity surprises, less cross-database maintenance."*

For Engineering

"7.0.0-beta2 sits on top of beta1—read both sets of notes together; beta2 walks back one beta1 behavior and adds one PostgreSQL quoter fix.

1. PHP 8.4+ floor. CI covers 8.4 and 8.5. Do not upgrade before the platform does.
2. ? placeholders are now namespaced. This is the headline change since beta1. where('id = ?', [5]) renders id = :__1__, and getBindValues() returns ['__1__' => 5] rather than [0 => 5]. Passing getBindValues() to execute() is unaffected; code that reached the value by number (getBindValues()[0], bindValue(0, ...)) must use the generated name.
   • Why: a statement never mixes ? with named placeholders—plain PDO rejects that on MySQL and PostgreSQL, and pdo_sqlite ran it with the ? unbound. It also means ? placeholders from separate where() calls no longer both claim index 0, which 3.x rendered against a single value.
   • What beta2 reverses: beta1 made two separate where() calls with ? throw. They don't anymore—each gets its own name. The net effect is fewer upgrade exceptions, not more.
3. Arity is enforced. Values given for ? must match ? one for one, or InvalidArgumentException—now for any count mismatch, where beta1 only threw for lists and sub-selects. A ? given no value (condition passed no values, or only named ones) is still kept for manual binding, as in 3.x. Values passed with a closure condition fill the closure's unvalued ? in reading order, under names taken before the closure runs: where(fn ($q) => $q->where('a = ?')->where('b = ?', [6]), [5]) renders a = :__1__ AND b = :__2__. ?? (PDO's literal-? escape), and ? inside comments or string literals, are not placeholders—and this is dialect-aware: on MySQL # starts a comment and -- only before whitespace, so a--? there is subtraction plus a placeholder; on PostgreSQL E'...' and $$...$$ are literals.
4. Resets drop generated values. resetWhere(), resetHaving(), resetTables(), resetUnions(), resetWith() release the generated values their clause bound—for ? values and lists alike—unless a rendered UNION branch still spells them, because nobody can rebind a generated name and a leftover value is a parameter PDO rejects at execute. Values bound under a name you wrote are kept, as before.
5. Duplicate named placeholders still throw. SET status = :status WHERE status = :status remains a LogicException—the silently-wrong-UPDATE case beta1 surfaced is still caught. Both union branches may still share one name for the same value, and names inside string literals, comments, or quoted identifiers don't count. Manual bindValue()/bindValues() still overwrite freely.
6. $text_pattern moved. A beta1-era subclass overriding Select::$text_pattern must override getTextPattern(); the reading moved up to AbstractQuery so conditions are scanned with it, the property no longer exists, and a property override would be ignored without a word.
7. Full native typing (unchanged from beta1). Every parameter and return declared from what the docblock claimed; 166 methods now declare static. Subclasses overriding Quoter::quoteName($spec), Select::limit(), etc. must match or fatal on load; same for redeclared properties (all typed except $builder). Two signatures widened (fromSubSelect()/joinSubSelect() take string|SelectInterface); three docblocks narrowed (Quoter::quoteNamesIn()/replaceNamesIn() return string; getLastInsertIdName() returns string|null).
8. strict_types is the caller's problem, not the package's. From a strict file, limit('5') throws—cast at the edge. From a non-strict file, scalars coerce as before; the only new failure is a non-coercible value, which used to silently become LIMIT 0.
9. CTEs are in the interfaces. All four query interfaces extend WithInterface. Out-of-package implementations must add with(), withRecursive(), resetWith(), hasWith(); extending the concrete query or using Common\WithTrait is the shortcut. Pgsql\Insert and Sqlite\Insert implement OnConflictUpdateInterface.
10. Exceptions are concrete and namespaced. LogicException, BadMethodCallException, InvalidArgumentException under Aura\SqlQuery\Exception, each extending its SPL counterpart and implementing ExceptionInterface (extends \Throwable) as the catch-all. The old Aura\SqlQuery\Exception interface is deprecated and keeps existing catches working until 8.x; direct instantiation or subclassing must move to a concrete class.
11. AbstractQuery::getStatement() is abstract. Query types extending AbstractQuery directly define their own statement assembly. subSelect() moved from Common\Select to AbstractQuery with a $source argument; rebuildCondAndBindValues() takes $clause.
12. PostgreSQL dollar-quoting fixed (#274). A new Pgsql\Quoter leaves $$...$$ / $tag$...$tag$ as written instead of quoting a dotted name inside it as table.column—where('a = $$t.c$$') previously compared a against "t"."c". Other dialects unchanged.

Plan: if you already spiked on beta1, re-run it against ^7.0@beta2 on a branch—the placeholder change is behavioral, not additive. Enable strict_types in hot query paths; log every LogicException (each is a pre-existing latent bug) and every InvalidArgumentException (each is a ?/value count mismatch); rename numeric bind-value access; replace $text_pattern overrides with getTextPattern(). Budget the subclass/interface signature work separately—that's still the bulk of the effort. No migrations or schema tooling; pair with Doctrine Migrations. Try it: convert one query class and one reporting query to withRecursive() and compare readability against the raw SQL it replaces."*