Use MySQL CASE in Laravel v5.2 queries

In this post, I’ll go over how to use MySQL CASE within Laravel v5.2 queries, give some use cases and also give some examples for my copy-paste homies.

Why Laravel v5.2?

Because sometimes the applications we work on are legacy and we play the hand we are dealt. 🙂 Also, when I was researching this originally, I found Laravel v5.2 query related posts and MySQL CASE relates posts but nothing that really put it together in the way I needed it to really understand it. So here goes…

What is MySQL CASE?

(If you already are familiar with MySQL CASE, you can skip this part)

MySQL CASE is basically the IF THEN conditional for a query. Let’s say you want to setup your query so that the alias “fruit” should be “apple” if the color value is red, “orange” if the color value is orange, or “banana” if the other two color value requirements are not met.

This is how you would write the example statement using CASE in your query:

SELECT CASE
  WHEN fruitColor = "red" THEN "apple"
  WHEN fruitColor = "orange" THEN "orange"
  ELSE "banana"    
END AS fruit FROM myTable;

Granted, this is a very simple example but don’t let the simplicity fool you. CASE is powerful. You can use it to normalize data and extrapolate values with the power of the conditional and it has quite a bit of flexibility. In this more advanced example, CASE is paired with subqueries so that subquery results are used both in the comparison and as the result if conditions are met:

SELECT CASE
  WHEN (SELECT taskId FROM taskTable where taskId = 1) !=
  (SELECT taskId FROM anotherTaskTable where subTaskId = 5)
  THEN (SELECT resultText FROM finalTable where resultId = 6)
  WHEN (SELECT taskId FROM taskTable where taskId = 35) !=
  (SELECT taskId FROM anotherTaskTable where subTaskId = 36)
  THEN (SELECT details FROM anotherTable where id = 6)    
END AS finalResultText FROM myTable;

As you can see, you can go pretty nuts with the queries within the CASE statement. I highly recommend simplicity as the best route but I also get that sometimes, it’s just that complicated. CASE to the rescue.

Now to Laravel v5.2 and CASE

In order to use CASE within the query object in Laravel v5.2, you have to first setup the query object to accept a raw sql query. In the example below, I am using the addSelect() method and the DB::raw() method to support the raw query execution. For me the addSelect() method seems to work better executing DB::raw() in situations where there are a lot of varied select statements and DB::raw() tells Laravel to process the text as MySQL query text. Keep in mind that you don’t have to use the addSelect() method. You should be able to use the select() method as well but DB:raw() is definitely needed to run CASE properly.

MyTable::addSelect(
   DB::raw("(SELECT (CASE
      WHEN fruitColor = "red" THEN "apple"
      WHEN fruitColor = "orange" THEN "orange"
      ELSE "banana"    
      END)) AS fruit")
  )->orderBy('fruit', 'ASC')->get(); 

And that is it…

Cool right? After working with this for a little while now, it has been a game changer in situations where I need to normalize data in the application I currently work with. Hopefully it does some game changing for you as well.

One last note: Remember that aliases cannot be used in other select statements so you may end up having to use the CASE statement within other select statements. Luckily, DB::raw() also accepts php variables so you can re-use the CASE statement as needed in different situations. The way to set this up is as follows:

$caseStatement = "(SELECT (CASE
      WHEN fruitColor = "red" THEN "apple"
      WHEN fruitColor = "orange" THEN "orange"
      ELSE "banana"    
      END)) AS fruit";

MyTable::addSelect(
   DB::raw($caseStatementToGetFruitValue)
  )->orderBy('fruit', 'ASC')->get(); 

I hope this has been helpful. Have a great day and happy coding.

Categories: Coding | Laravel | My Blurbs