aglipanci/laravel-eloquent-case

Adds CASE statement support to Laravel Query Builder.

3.0.0 2024-03-12 15:05 UTC

Requires

• php: ^7.4|^8.0
• illuminate/database: ^8.0|^9.0|^10|^11
• illuminate/support: ^8.0|^9.0|^10|^11

Requires (Dev)

• friendsofphp/php-cs-fixer: ^3.51
• nunomaduro/larastan: ^1.0
• orchestra/testbench: ^6.23.0|^7.0.0
• phpunit/phpunit: ^9.3.9

MIT

• Agli Panci
• Eduard Lleshi

README

This packages adds CASE statement support to Laravel Query Builder. It supports Laravel 8.x, 9.x, 10.x & 11.x.

Usage

Add a CASE statement select on a Laravel Query

use App\Models\Invoice;
use AgliPanci\LaravelCase\Query\CaseBuilder;

$invoices = Invoice::query()
            ->case(function (CaseBuilder $case) {
                $case->when('balance', '<', 0)->then('Overpaid')
                    ->when('balance', 0)->then('Paid')
                    ->else('Balance Due');
            }, 'payment_status')
            ->get();

Produces the following SQL query:

SELECT
  ( CASE
      WHEN `balance` < 0 THEN 'Overpaid'
      WHEN `balance` = 0 THEN 'Paid'
      ELSE 'Balance Due'
    END ) AS `payment_status`
FROM
  `invoices`

Build the case query separately

use App\Models\Invoice;
use AgliPanci\LaravelCase\Facades\CaseBuilder;

$caseQuery = CaseBuilder::when('balance', 0)->then('Paid')
                    ->when('balance', '>', 0)->then('Balance Due');
                    
$invoices = Invoice::query()
            ->case($caseQuery, 'payment_status')
            ->get();

Raw CASE conditions

use App\Models\Invoice;
use AgliPanci\LaravelCase\Facades\CaseBuilder;

$caseQuery = CaseBuilder::whenRaw('balance = ?', [0])->thenRaw("'Paid'")
                    ->elseRaw("'N/A'")
                    
$invoices = Invoice::query()
            ->case($caseQuery, 'payment_status')
            ->get();

Use as raw SELECT

use App\Models\Invoice;
use \AgliPanci\LaravelCase\Facades\CaseBuilder;

$caseQuery = CaseBuilder::whenRaw('balance = ?', [0])->thenRaw("'Paid'")
                    ->elseRaw("'N/A'")
                    
$invoices = Invoice::query()
            ->selectRaw($caseQuery->toRaw())
            ->get();

Available methods

use AgliPanci\LaravelCase\Facades\CaseBuilder;

$caseQuery = CaseBuilder::whenRaw('balance = ?', [0])->thenRaw("'Paid'")
                    ->elseRaw("'N/A'");
                    
// Get the SQL representation of the query.                    
$caseQuery->toSql(); 

// Get the query bindings.
$caseQuery->getBindings(); 

// Get the SQL representation of the query with bindings.
$caseQuery->toRaw(); 

 // Get an Illuminate\Database\Query\Builder instance.
$caseQuery->toQuery();

Installation

You can install the package via composer:

composer require aglipanci/laravel-eloquent-case

Testing

composer test

Changelog

Please see CHANGELOG for more information what has changed recently.

Security

If you discover any security related issues, please email [e-mail] instead of using the issue tracker.

Credits

• Agli Pançi
• Eduard Lleshi
• All Contributors

License

The MIT License (MIT). Please see License File for more information.