How to run raw query laravel eloquent ?

Sometimes in laravel we wanted to run raw query like select, insert, delete, alter etc. Laravel have multiple ways to run a raw query using select, prepare or statement method in db builder. In this article i will show you to run the raw query in laravel.

To understand this we will take simple examples of multiple solutions. So here is the list to execute the raw query using DB facade in laravel

1. Select
2. Insert
3. Update
4. Delete
5. Statement
6. unprepared
7. raw
8. selectRaw
9. whereRaw
10. orderByRaw
11. groupByRaw
12. havingRaw

As you can we have multiple ways to create raw query and use of it in different cases.

Select() method of DB class

Select method is used to execute select statement in db builder as below

 
public function index()
{
    $users = \DB::select('SELECT * from users');
    dd($user);
}

Output of above code:

array:1 [▼
  0 => {#1268 ▼
    +"id": 1
    +"name": "Aman"
    +"email": "Aman@yopmail.com"
    +"email_verified_at": null
    +"password": "2121212"
    +"remember_token": null
    +"created_at": null
    +"updated_at": null
  }
]

Passing user inputs to select statement

 
public function index(Request $request)
{
    $users = \DB::select('SELECT * from users where email= ? ',[$request->email]);
    dd($user);
}

Insert() method of DB class

Select method is used to execute select statement in db builder as below

 
public function index(Request $request)
{
    $users = \DB::insert('INSERT INTO `users` (`name`, `email`) VALUES (?, ?)',[$request->email,$request->name]);
   
}

Update() method of DB class

Select method is used to execute select statement in db builder as below

 
public function index(Request $request)
{
    $users = \DB::insert('Update `users` set name =  ? where email = ?',[ $request->name,$request->email]);  
}

delete() method of DB class

Select method is used to execute select statement in db builder as below

 
public function index(Request $request)
{
    $users = \DB::delete('DELETE from `users` where  email = ?',[$request->email]);  
}

Statement() method of DB class

Statement method is used to run alter, drop, create or we can say which doesn’t return any values

 
public function index(Request $request)
{
    $users = \DB::statement('ALTER TABLE `users` CHANGE `name` `full_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ');  
}

Unprepared() method of DB class

Unprepared is almost same as Statement method but it does not check of SQL injection and we can run any type of query which doesn’t return any data as below

 
public function index(Request $request)
{
    $users = \DB::unprepared('ALTER TABLE `users` CHANGE `name` `full_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ');  
}

raw() method of DB class

Raw method is used to execute raw query in our in build laravel eloquent methods like where, select, order by as below

 
public function index(Request $request)
{
    $users = \DB::table('users')->where(\DB::raw("email like 'a'"));
    //or
    $users = \DB::table('users')->orderBy(\DB::raw("email")); 
}

selectRaw() method of DB class

selectRaw method is used to execute queries in select statement

 
public function index(Request $request)
{
    $users = \DB::table('users')->select("email, concat(first_name, last_name)")->get();
}

whereRaw() method of DB class

whereRaw method is used to execute queries in where clause

 
public function index(Request $request)
{
    $users = \DB::table('users')->whereRaw('email like "%a%"')->get();
}

Also Read : How to use conditional where clause in Laravel 8 eloquent ?

orderByRaw() method of DB class

orderByRaw method is used to execute queries in orderBy clause

 
public function index(Request $request)
{
    $users = \DB::table('users')->orderByRaw('if(status=1,email,id)')->get();
}

groupByRaw() method of DB class

groupByRaw method is used to execute queries in groupBy clause

 
public function index(Request $request)
{
    $users = \DB::table('users')->groupByRaw('if(status=1,email,id)')->get();
}