Home » How To Use CASE Statement In Laravel

How To Use CASE Statement In Laravel

Last updated on June 12, 2021 by

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:

  1. Laravel: Blade Switch Case Statement Example
  2. Laravel: Switch Case Statement In Controller Example
  3. Laravel: Change Column Type In Migration
  4. Laravel: Change Column Name In Migration
  5. How To Use Where Date Between In Laravel
  6. How To Add Laravel Next Prev Pagination
  7. Laravel Remove Column From Table In Migration
  8. Laravel: Get Month Name From Date
  9. Laravel: Increase Quantity If Product Already Exists In Cart
  10. How To Update Pivot Table In Laravel
  11. How To Dynamic iFrame URL In Elementor
  12. How To Handle Failed Jobs In Laravel
  13. How To Remove WooCommerce Data After Uninstall
  14. How To Get Latest Records In Laravel
  15. How To Break Nested Loops In PHP Or Laravel
  16. How To Pass Laravel URL Parameter
  17. Laravel Run Specific Migration
  18. How To Fix Elementor Icons Not Showing

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!

 
 

Leave a Comment

*

code