Laravel Eloquent Case Laravel Package

Getting Started

Install with composer require aglipanci/laravel-eloquent-case. The service provider is auto-discovered and there is no config file to publish. Start by reading the README and src/Query/CaseBuilder.php; the day-to-day surface is the fluent case()->when()->then()->else()->as() chain.

The first use case is replacing a PHP-side match/switch label map with a SQL CASE, so filtering, sorting, and grouping can happen in the database:

use AgliPanci\LaravelCase\Query\CaseBuilder;

$statusLabel = CaseBuilder::case()
    ->when('orders.status', '=', 'paid')->then('Paid')
    ->when('orders.status', '=', 'pending')->then('Pending')
    ->else('Unknown')
    ->as('status_label');

$orders = Order::query()
    ->select('orders.*')
    ->selectRaw((string) $statusLabel)
    ->get();

As of 5.1.0 the generated identifiers follow the active Laravel connection grammar, so the same expression is portable across MySQL/MariaDB (`backticks`), Postgres/SQLite ("double quotes"), and SQL Server ([brackets]).

Implementation Patterns

Centralize label maps with a helper. Avoid repeating WHEN chains across controllers and scopes:

function statusLabelCase(string $column = 'status'): CaseBuilder
{
    $case = CaseBuilder::case();

    foreach (OrderStatus::labels() as $value => $label) {
        $case->when($column, '=', $value)->then($label);
    }

    return $case->else('Unknown')->as('status_label');
}

Use it in a local scope so every consumer gets the same derived column:

public function scopeWithStatusLabel(Builder $query): Builder
{
    return $query->selectRaw((string) statusLabelCase('orders.status'));
}

Custom sort order. CASE is useful for business-priority ordering that a normal ORDER BY cannot express:

$query->orderByRaw((string) CaseBuilder::case()
    ->when('status', '=', 'urgent')->then(0)
    ->when('status', '=', 'normal')->then(1)
    ->else(2));

Conditional aggregates for reporting. Embed the fragment inside an aggregate:

$query->selectRaw('SUM(' . (string) CaseBuilder::case()
    ->when('status', '=', 'paid')->then(1)
    ->else(0) . ') as paid_count');

Build per connection in multi-database apps. Construct the expression after the model/connection is selected (Model::on('pgsql')) and keep the builder close to the query it feeds. If you cache generated SQL strings, include the connection name in the cache key.

Gotchas and Tips

• 5.1.0 changes generated SQL. The package no longer assumes MySQL-style backtick quoting. Tests that assert exact SQL strings, snapshot raw SQL, or compare query logs will need updating. Prefer asserting results (assertDatabaseHas, returned attributes) over exact SQL.
• Invalidate cached SQL. If your application caches compiled CASE fragments or generated query strings, flush them on deploy; cached fragments may contain the old quoting.
• Grammar follows the active connection. In apps with multiple connections, a CaseBuilder instance built while one connection is active will quote for that grammar. Build the expression on the same connection as the query that uses it.
• Values are inlined into SQL. Treat then() and else() arguments as SQL literals, not bindings. Never pass raw request input; use whitelisted maps, enums, or parameter binding with DB::raw/selectRaw placeholders for dynamic values.
• Be explicit about operators. Use =, >, LIKE, IS NULL, etc. explicitly; do not rely on a two-argument when() default if you have not verified it in the source.
• NULL does not match =. A branch like when('deleted_at', '=', null) will not behave as expected. Add explicit IS NULL / IS NOT NULL handling.
• Omitting else() produces NULL. If downstream DTOs or API resources expect a non-null label, always add an else() fallback.
• Aliases are raw attributes. A status_label selected via selectRaw is not automatically a model attribute in toArray(); add it to $appends or map it in a resource. It also bypasses casts, so it will be a string.
• CASE in WHERE/ORDER BY can defeat indexes. Filter on indexed columns with normal where() calls where possible, and use CASE for presentation or derived sort priority rather than primary filtering.
• Driver-specific literals. Booleans and dates can render differently across grammars. Keep then()/else() values as driver-neutral scalars or strings when you need cross-database portability.
• Extension point. If the builder does not support an operator you need, fall back to DB::raw for that specific branch or write the full expression as raw SQL rather than fighting the fluent API.