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:

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:

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:

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.

Have you tried our tool to generate Laravel adminpanel without a line of code?
Go to QuickAdminPanel.com

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:

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.

Check out my new online course: Creating Invoices with Laravel 5.6