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 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:

  (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')
  (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.

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

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)

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.

Like our articles?
Check out our Laravel online courses!


  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”));
    DB::statement(“SELECT name, surname, (CASE WHEN (gender = 1) THEN ‘M’ ELSE ‘F’ END) as gender_text FROM users”);

    • 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’)
    ->orderBy(‘id’, ‘desc’)

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

    then how to select skills name in this query.

  4. Thank you for this example, it’s really helpful. Could you tell me how would you in this case filter by gender? Is it possible to just add ->where(‘gender_text’, ‘F’)?
    Obviously I’m asking because I have something similar and I can’t for the life of me make it work. It returns the field properly, but I don’t know how to filter by just one gender.


Please enter your comment!
Please enter your name here