How to Use LARAVEL DB Raw Query?

Spread the love

Hello Guys,

This discussion centers on optimizing your usage of “LARAVEL DB Raw Query” for improved SEO. We’ll explore a real-world example of executing a raw Laravel query, specifically leveraging Laravel’s DB::raw query feature. In this article, we’ll provide a clear demonstration of record counting using a Laravel DB raw query.

In the Laravel framework, you have the ability to tap into the potential of DB::raw() queries. This feature can be efficiently applied in combination with select clauses, join queries, and where conditions. In this article, we’ll showcase its usage through four distinct instances, as detailed below.

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

Also Read: How To Send Email In Laravel 9

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

Also Read: How To Create Repeater Field Using Bootstrap

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

Also Read: How to use laravel cookies – Get, Set, Delete with example

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

Also Read: Laravel 10 New Features And Updates Tutorial

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

<?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);
    }
}

Embrace the elite realm of Laravel insights – your compass to the best Laravel blog awaits.

Leave a Comment