Laravel Query Enrich Laravel Package

Getting Started

1. Install with Composer and let Laravel package discovery register the service provider:

   composer require sbamtr/laravel-query-enrich
   

   1.4.0 adds Laravel 13.x compatibility. Check the illuminate/* constraint in composer.json before upgrading a project that is still on 10.x — do not assume the new release still supports your version.

2. Read the README’s method list and then the provider in vendor/sbamtr/laravel-query-enrich/src/. This package is not a when() / whereLike convenience wrapper — it is about generating SQL you would otherwise write by hand:

   • a CASE expression builder (case → when → then → else → as → end),
   • DB function helpers (aggregate/scalar functions, usable as select or order expressions, and as of 1.4.0 able to take a subquery argument),
   • exists / notExists condition helpers.

   Confirm which builder the macros land on (Illuminate\Database\Query\Builder vs Illuminate\Database\Eloquent\Builder). That decides whether they work inside repositories that mix DB::table() and Eloquent.

3. Verify the real signatures in tinker before writing code. Note that a case builder is a separate object until you call end(), so hasMacro() on a query builder is not enough to confirm the chain:

   $q = User::query();
   
   collect(['case', 'dbFunction', 'exists'])
       ->mapWithKeys(fn ($m) => [$m => $q->hasMacro($m)]);
   
   // then dump the SQL of a full chain
   dump(User::query()->select('*')->/* case chain from the README */->toRawSql());
   

4. First real use case: a reporting or dashboard query where you need a derived column and to filter/sort on it — bucketing order totals into value bands, deriving a status label, or ordering by a conditional score. Replace one hand-written DB::raw("CASE WHEN ... END") with the package’s builder, keep the response shape identical, and add a feature test that asserts both the result rows and the generated SQL. On MySQL/Postgres only at first; SQLite exists behaviour changed in 1.4.0 (see Gotchas).

Implementation Patterns

• Keep helpers at the query edge. Use them in controllers, query/filter objects, or model scopes. Do not leak the case builder or DBFunction calls into jobs, actions, or services that should stay persistence-agnostic.

• Encapsulate a CASE column in a scope. Because the chain is verbose and mutable, wrap it once and call it from everywhere:

  // app/Models/Order.php
  public function scopeWithValueBand(Builder $query): Builder
  {
      return $query->select('*')->/* case chain */->as('value_band')->end();
  }
  

  Application code then calls Order::query()->withValueBand()->where('value_band', 'high'). This keeps IDE/static-analysis noise in one place and makes the helper swappable if the package changes.

• Use case->when for derived classification, not for optional filters. when(..., 'isNull') (new in 1.4.0) is the clean way to build a branch on a nullable column — e.g. when('deleted_at', 'isNull')->then('archived')->else('active'). Use ordinary ->when($filter, ...) for optional user filters; mixing the two makes the chain hard to read.

• Pass backed enums directly. 1.4.0 supports enums as when/then/function arguments. If your model casts a status column to a PHP enum, pass the enum case instead of $enum->value — but confirm in getBindings() that the scalar value lands in the binding list, not the enum object, and that your database stores the same representation you cast to.

• Subqueries as function arguments. With 1.4.0 you can pass a subquery to a DBFunction. Useful for “aggregate over a subquery” without dropping to DB::raw(). Keep the subquery on the same builder instance you are enriching where possible so connection and grammar match; a subquery built from a different connection can generate the wrong SQL dialect.

• Order by a CASE expression deliberately. Ordering on the SELECT alias works on MySQL but not universally; on PostgreSQL you generally need the expression repeated in ORDER BY or an ordinal. Test whichever approach you pick against your production driver, and prefer ordering by the alias only if you have pinned the driver.

• Pin the version if you depend on the generated SQL. 1.4.0 changed the SQLite exists output and how DBFunction handles subquery bindings. A minor release did change behaviour here — pin ^1.4 (or the exact tag) and read the changelog before upgrading.

Gotchas and Tips

• The case builder is not the query builder. Intermediate objects in the chain do not respond to where, paginate, or get. If you get “method does not exist”, you have almost certainly forgotten ->end() or ->as().

• = null versus isNull. Before 1.4.0 you could not express an IS NULL branch through case->when; the comparison generated = ? with a null binding, which never matches. Use the 'isNull' parameter explicitly for nullable columns, and after upgrading re-check any CASE expressions where you previously worked around this with DB::raw.

• SQLite exists behaviour changed in 1.4.0. If your CI runs SQLite and you assert on generated SQL, existing expectations may flip. Re-run ->toSql() snapshots after the bump; if you relied on the old (incorrect) form in production code, verify locally before deploying.

• Bindings can shift. A subquery argument inside a DBFunction adds bindings mid-expression. Assert on the full ->getBindings() array rather than positional [0], and be aware that where clauses added after the function call append, not interleave.

• Enums are not strings everywhere. A pure (non-backed) enum has no scalar value to bind. Only backed enums make sense as query arguments; if one slips through, you get an “Object of class could not be converted to string” error at execution, not at build time.

• Driver differences matter. Case-insensitivity, ILIKE, JSON extraction, boolean literals, and EXISTS formatting differ between MySQL, PostgreSQL, and SQLite. Test against the database you deploy on, not only SQLite in CI.

• Never pass user input as a column or direction. Any helper that takes a column name, sort direction, or raw expression is a SQL-injection boundary. Whitelist column names and directions from validated input.

• Debug with raw SQL and bindings. ->toRawSql() is the fastest way to see what the case/DBFunction helpers actually produced. When output surprises you, log it with DB::listen() rather than re-reading the chain.

• Check provider boot order when macros are missing. If a helper works in a controller but not in a queued job or package service provider, the macro may be registered later than your call site. Confirm discovery with php artisan about, and avoid calling these macros during early provider boot.

• Macro collisions are silent. If your AppServiceProvider or another package registers the same macro name, the last one wins. Use Builder::hasMacro() and keep your own macros namespaced or wrapped in scopes.

• Prefer local extension over patching. If a helper is missing a case you need, add a small macro or a DB::raw() inside one scope rather than patching vendor/. The package is MIT-licensed and small; copying a pattern from its source is usually faster than waiting on an issue.

• Small ecosystem. Three new contributors landed in this release, which is a good sign, but there is still little community documentation. Budget time to read the source and to write your own regression tests around every CASE expression and function call you adopt — those tests are what will tell you whether 1.5.0 changes anything.