laravel db Raw SQL Queries select insert and Update

Use Raw Database Queries in Laravel, DB::raw() is used to make arbitrary SQL commands which are not parsed any advance by the query builder. They therefore can make a vector for attack more SQL injection.

Laravel db raw where clause example

In this Big article We will give 7+ method to use Raw Database Queries sql aggregate function with Orwhere clause in laravel. there are Imp 2 method : first is a suing DB::raw() instand of laravel db::select and second is a whereRaw() with subquery in laravel. Therefor here simply We will give you very useful example with Laravel Dd() And Dump() on all the laravel method, you can understand step by step as well as use it well in your laravel any 5,6,7 projects. Also you can learn my prev laravel database tutorial.

using DB::raw()

$members = \DB::table("members")
        ->where(DB::raw("(DATE_FORMAT(joining_dates,'%d-%m-%Y'))"),'19-01-2018')
        ->get();
dd($members);

using whereRaw()

$members = \DB::table("members")
        ->whereRaw("DATE_FORMAT(joining_dates,'%d-%m-%Y') = '25-04-2021'")
        ->get();
dd($members);

laravel raw query with parameters Examples

• selectRaw() with Avg/Sum/Count Calculations
• Raw Queries in Laravel
• avg() and count() in Laravel using selectRaw()
• Using groupByRaw, orderByRaw and havingRaw
• sub-query with selectRaw()
• using DB::select()

selectRaw() with Avg/Sum/Count Calculations

If you reqired to execute groupBy() as well as then use some aggregation function from MySQL, such as a AVG() or COUNT(), This is a very helpful to execute a simple sub Raw Query for that particular part.

$members = DB::table('members')
    ->selectRaw('count(*) as member_count, is_active')
    ->where('is_active', '<>', 1)
    ->groupBy('is_active')
    ->get();

dump($members);

Raw Queries in Laravel

$computers = DB::table('computers')
    ->leftjoin('brands','brands.computer_id','=','computers.id')
    ->selectRaw('COUNT(*) as nbr', 'computers.*')
    ->groupBy('computers.id')
    ->get();

dump($computers);

avg() and count() in Laravel using selectRaw()

More useful laravel example below – I can even execute the avg() with selectRaw as well as count() in the same sql statement.

$computers = DB::table('computers')
    ->selectRaw('brands.name as company_name, avg(computer_price) as avg_computer_price, count(*) as employee_count')
    ->join('brands', 'computers.brand_id', '=', 'brands.id')
    ->groupBy('brands.id')
    ->orderByDesc('avg_computer_price')
    ->get();

dump($computers);

Using groupByRaw, orderByRaw and havingRaw

We have 3+ methods such as a groupByRaw() as well as orderByRaw() for this. Also, I can use additional more extra “where” statement with sub-query with selectRaw() after grouping, by “having” clause SQL statement with use a havingRaw() with get data.

$members = Member::selectRaw('YEAR(joining_dates) as year, COUNT(id) as salary')
    ->groupByRaw('YEAR(joining_dates)')
    ->havingRaw('YEAR(joining_dates) > 1992')
    ->orderByRaw('YEAR(joining_dates)')
    ->get();

dump($members);    

sub-query with selectRaw()

$tamilrokers = TamilRokers::select('id', 'movie_name')
    ->selectRaw('visitors - robot_type AS uniq_users')
    ->get();
dump($tamilrokers);    

$members = DB::table('members')
    ->select('member_name', 'member_last_name')  
    ->selectRaw("(CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as msymbol")
    ->get();

dump($members);    

using DB::select()

$results = DB::select('select * from members where id = ?', [1]);
dd($results);

using DB::statement() in laravel Migrations

DB::statement('UPDATE members SET member_id = 1 WHERE member_id IS NULL AND YEAR(updated_at) > 2021');

DB::statement('DROP TABLE members');
DB::statement('ALTER TABLE mcards AUTO_INCREMENT=123');

Use Raw Database Queries in Laravel

In Laravel, you can use raw database queries to perform SQL queries directly on the database. Here’s an example of how to use raw queries in Laravel:

use Illuminate\Support\Facades\DB;

// Execute a raw SQL query
$results = DB::select('SELECT * FROM users WHERE id = ?', [1]);

// Execute a raw SQL query with a named parameter
$results = DB::select('SELECT * FROM users WHERE name = :name', ['name' => 'John']);

// Execute a raw SQL query with multiple parameters
$results = DB::select('SELECT * FROM users WHERE name = ? AND age > ?', ['John', 25]);

// Execute a raw SQL query that inserts data into the database
DB::insert('INSERT INTO users (name, email, password) VALUES (?, ?, ?)', ['John', '[email protected]', 'password']);

// Execute a raw SQL query that updates data in the database
DB::update('UPDATE users SET name = ? WHERE id = ?', ['John Doe', 1]);

// Execute a raw SQL query that deletes data from the database
DB::delete('DELETE FROM users WHERE id = ?', [1]);

In the above example, we use the DB facade provided by Laravel to execute raw SQL queries. We can pass parameters to the SQL query using either an array or named parameters. Laravel will automatically handle the parameter binding to prevent SQL injection attacks.

Note that using raw queries can make your code less readable and harder to maintain. Laravel also provides an ORM (Object-Relational Mapping) called Eloquent which allows you to work with the database in a more intuitive and expressive way. It’s recommended to use Eloquent whenever possible, and only resort to raw queries when necessary.