Select with DB::raw() - make your database work

When selecting data form the database, sometimes you need to make some extra filtering with results - with some if-else statements and similar. With Laravel - you can achieve that with Accessor fields or just looping through results in PHP. But there is a more effective way - to move the filters to the database query itself.

Imagine a real scenario - in DB table you have a field gender with values - 1 for Male, and 2 for Female. But on the page you need to show the texts, not the numbers. So straightforward way would be IF-statement in blade:

{{ ($user->gender == 1) ? 'Male' : 'Female' }}

But sometimes you don't have that luxury of filtering - you need the data to come back already processed - the best example is working with Datatables.net from server-side with AJAX calls, it is expecting a valid ready-made JSON as a response.

So, we would have to run SQL query with IF-statement. In fact, in MySQL it's CASE-WHEN statement, something like this:

SELECT
  (CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) AS gender_text
  FROM users;

Now, how can you do it in Laravel? In Query Builder you have a select() method where you just list the fields without too much logic, right? Yes and no. You can pass a such-called "raw query" as a parameter to select(). Like this:

$users = DB::table('users')
  ->select(DB::raw("
  name,
  surname,
  (CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as gender_text")
);

As you can see, a method DB::raw() (don't forget to use Illuminate\Support\Facades\DB) allows you to select whatever you want and basically write raw SQL statements.

Another popular usage of this is grouping statements. You need to launch a MySQL function like COUNT() - so you can use a DB::raw() for it. Here's an example from official Laravel documentation:

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

So, the point is that Laravel Query Builder has a lot of helpful methods, but it doesn't restrict you from running raw SQL queries (or their parts) when needed.