Laravel db::raw query example. In this tutorial, you will learn how to use db raw query in laravel to select, insert, update, and delete data from database.
Note that, Using the DB::raw() eloquent method, you can add subquery in laravel queries.
If you may want to insert, retrieve, update, and delete data into database table, use the laravel eloquent methods.
For retrieve all data from the database table, you can use the following query:
$users = User::get();
For delete data from the database table, you can use the following query:
$users = User::where('columnname', columnvalue)->delete();
Laravel db::raw query example
- Example 1: DB raw With Select Clause Query in laravel
- Example 2: DB raw query with join in laravel
- Example 3: Using DB Raw Get Max Value in Laravel Query
- Example 4: Insert Query using DB Raw
- Example 5: UPDATE Query using DB::raw
Example 1: DB raw With Select Clause Query in laravel
When you write query in your controller using model or query builder and may need to use a raw expression in a query.
You can write as follow:
$users = User::select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
Example 2: DB raw query with join in laravel
How to write db raw query with laravel joins.
You can write as follows:
$data = DB::table("products")
->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();
print_r($data);
Example 3: Using DB Raw Get Max Value in Laravel Query
Using the db raw, you can get max value from db.
You can write as follow:
\DB::table('orders')->where('id', \DB::raw("(select max(`id`) from orders)"))->get();
Example 4: Insert Query using DB Raw
In this example, the DB::raw method is used to insert the current timestamp directly into the created_at and updated_at columns.
use Illuminate\Support\Facades\DB;
DB::table('users')->insert([
'name' => 'John Doe',
'email' => '[email protected]',
'created_at' => DB::raw('NOW()'),
'updated_at' => DB::raw('NOW()')
]);
Example 5: UPDATE Query using DB::raw
In this example, the DB::raw method is used to update the updated_at column with the current timestamp when the stock of a product is less than 10.
use Illuminate\Support\Facades\DB;
DB::table('products')
->where('stock', '<', 10)
->update([
'status' => 'low_stock',
'updated_at' => DB::raw('NOW()')
]);
