ItSolutionStuff.com

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