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: Laravel Eloquent: Expert Level

8 thoughts on “Select with DB::raw() – make your database work

  1. I know it was just one example, but if you’re creating your own database schema, I wouldn’t recommend actually using 0 and 1 for male and female. That just makes every bit of code that uses that database have to be more complicated and confusing than it should be just to save a few bytes per row. It’s not worth it.

    Storage space and memory used to be more limited than it is today, so that kind of choice used to make sense but no longer does on modern servers for most situations. (But if your database supports “enum”, that option can give you the best of both worlds.)

  2. I have 2 questions:

    1 – what is the registrants table specified in the query?

    2 – What would be the difference from these 2 statement in terms of legibility and speed?
    $users = DB::table(‘users’)
    ->select(DB::raw(“name, surname, (CASE WHEN (registrants.gender = 1) THEN ‘M’ ELSE ‘F’ END) as gender_text”));
    and
    DB::statement(“SELECT name, surname, (CASE WHEN (gender = 1) THEN ‘M’ ELSE ‘F’ END) as gender_text FROM users”);

    1. Hi Raul,
      1. Thanks for spotting, forgot to remove registrants while copying from the other code of my project.
      2. Speed would be probably the same, first query is better if you want to join something or add more parts to query builder with Laravel functions, and statement is just a statement where you write your raw query – plain SQL.

  3. My Query is like this

    $jobDetails = DB::table(‘job_details’)
    ->select(‘id’,’job_title’,’job_skills’,’job_images’,’job_subtitle’,’job_cost_min’,’job_cost_max’,’job_description’,’job_availble_for’)
    ->orderBy(‘id’, ‘desc’)
    ->where(‘status’,’Active’)
    ->get();

    and job_skills contains array of skills id like [‘1′,’2′,’4′,’9′,’5’]

    then how to select skills name in this query.

Leave a Reply

Your email address will not be published. Required fields are marked *