How To Use CASE Statement In Laravel

In this tutorial, we will learn about how to use the CASE statement in Laravel. CASE statement works the same as the switch case or IF ELSE IF statement of PHP. So let’s just explore it.

Table of Contents

1. How CASE Statement Works

2. Use Laravel CASE In Query

3. Use Accessor Instead Of CASE Statement (More Laravel Approach)

01 How CASE Statement Works

The CASE statement goes through all the conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result.

If no conditions are true, it will return the value in the ELSE clause.

If you don’t define the ELSE clause and no conditions are true then it will return the NULL.

Example Of MySQL Case Function

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END
FROM OrderDetails;

02 Use Laravel CASE In Query

You can achieve the CASE statement in Laravel query using Raw string expression provided by the DB facade.

Our CASE query look like below in Laravel:

$orderDetails = OrderDetails::select("OrderID, Quantity",
    \DB::raw('(CASE 
        WHEN Quantity > "30" THEN "The quantity is greater than 30" 
        WHEN Quantity = "30" THEN "The quantity is 30"
        ELSE "The quantity is under 30"
        END) AS NewQuantity'))
->get();

dd($orderDetails);

Notes: Raw statements will be injected into the query as strings, so you should be extremely careful to avoid creating SQL injection vulnerabilities.

03 Use Accessor Instead Of CASE Statement (More Laravel Approach)

Sometimes, we don’t like to write a query like above using DB::raw instead of that, we should use something cool like Accessors.

The Accessors are used to format the attribute values when we retrieve them from the database.

Let’s suppose if we can add an additional custom column named "quantity_status" based on the quantity column. And when you access the $document->quantity_status column then it will return the quantity status as wanted to achieve in the above CASE statement. Hope you are getting it.

Let’s now add the new "quantity_status" column to the OrderDetails model. To do that you need to first add the protected $appends accessor.

app/Models/OrderDetails.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class OrderDetails extends Model
{ 
     /**
     * The accessors to append to the model's array form.
     *
     * @var array
     */

    protected $appends = ['quantity_status'];
}

Then we need to define an accessor method with get{Attribute}Attribute in your model where {Attribute} is the “studly” cased name of the column you wish to access.

So our method name will be getOrderStatusAttribute() as per our example. If your custom column is path_name then your method name will be getPathNameAttribute(). Let’s add our method in the model and our full model look like below:

app/Models/OrderDetails.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Storage;

class OrderDetails extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'order_id', 'description', 'quantity',
    ];

     /**
     * The accessors to append to the model's array form.
     *
     * @var array
     */

    protected $appends = ['quantity_status'];
 
    public function getOrderStatusAttribute()
    {
        if ( $this->quantity > 30 ):
            return "The quantity is greater than 30";
        else if ( $this->quantity === 30 ):
            return "The quantity is 30";
        else:
            return "The quantity is under 30";
        endif;
    }
}

Then it’s time to fetch the data and access our newly created custom column in Laravel. You can do it in the controllers or test it using Tinker.

app/Http/Controllers/OrderController.php

<?php

namespace App\Http\Controllers;

use App\Models\OrderDetails;

class OrderController extends Controller
{
    public function index()
    {   
        $orderDetails = OrderDetails::find(4);

        dd($document->quantity_status);
    }
}

Additionally, read our guide:

That’s it from our end. We hope this article helped you to learn how to use the MySQL CASE statement in the Laravel query or Eloquent model.

Please let us know in the comments if everything worked as expected, your issues, or any questions. If you think this article saved your time & money, please do comment, share, like & subscribe. Thank you for reading this post 🙂 Keep Smiling! Happy Coding!