How to Use DB Raw Query in Laravel?

Hello Folks,

Today, how to use db raw in laravel is our main topic. let’s discuss about laravel raw query example. We will use laravel DB::raw query. This post will give you a simple example of laravel db raw query count.

There is a way to use DB::raw() query in laravel. we can use with the select statement, join query, and where condition. here I will give you four examples as like below:

1. Example 1: DB Raw with Select Clause Query in Laravel

2. Example 2: DB Raw with Count Query in Laravel

3. Example 3: DB Raw with Join Query in Laravel

4. Example 4: DB Raw with Where Condition Query in Laravel

Let's see one by one example:

Example 1: DB Raw with Select Clause Query in Laravel

app/Http/Controllers/DemoController.php

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\User;
use DB;
  
class DemoController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index(Request $request)
    {
        $users = User::select(DB::raw("
                          name,
                          email,
                          (CASE WHEN (type = 1) THEN 'Admin' ELSE 'User' END) as user_type"))
                     ->take(3)
                     ->get();
  
        dd($users);
    }
}

Output:

Array
(
    [0] => Array
        (
            [name] => Hardik Savani
            [email] => aatmaninfotech@gmail.com
            [user_type] => Admin
        )
    [1] => Array
        (
            [name] => Vimal Kashiyani
            [email] => vimal@gmail.com
            [user_type] => User
        )
    [2] => Array
        (
            [name] => Deshaun Rowe Sr.
            [email] => gstoltenberg@example.org
            [user_type] => Admin
        )
)

Example 2: DB Raw with Count Query in Laravel

app/Http/Controllers/DemoController.php

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\User;
use DB;
  
class DemoController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index(Request $request)
    {
        $users = User::select(DB::raw('type, count(*) as user_count'))
                     ->groupBy('type')
                     ->get();
  
        dd($users);
    }
}

Output:

Array
(
    [0] => Array
        (
            [type] => 1
            [user_count] => 3
        )
    [1] => Array
        (
            [type] => 0
            [user_count] => 42
        )
)

Example 3: DB Raw with Join Query in Laravel

app/Http/Controllers/DemoController.php

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\Product;
use DB;
  
class DemoController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index(Request $request)
    {
        $data = Product::select("products.*", "product_stock.quantity_group")
              ->join(DB::raw("(SELECT 
                    product_stock.id_product,
                    GROUP_CONCAT(product_stock.quantity) as quantity_group
                  FROM product_stock
                    GROUP BY product_stock.id_product
                  ) as product_stock"),function($join){
                    $join->on("product_stock.id_product","=","products.id");
              })
              ->groupBy("products.id")
              ->get();
  
        dd($data);
    }
}

Example 4: DB Raw with Where Condition Query in Laravel

app/Http/Controllers/DemoController.php

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\Order;
use DB;
  
class DemoController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index(Request $request)
    {
        $data = Order::where('id', DB::raw("(select max(`id`) from orders)"))->get();
  
        dd($data);
    }
}

I hope it can help you...