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');


Related FAQ

Here are some more FAQ related to this Article:

  1.   join query in codeigniter Example Tutorial
  2.   Laravel 6 Advanced Where Clauses Tutorial With Example
  3.   Get Current date time in laravel Timezone
  4.   PHP prepend leading zero prefixes after number
  5.   order by multiple columns(fields) laravel query builder
  6.   wheredate in laravel | Eloquent date filtering : whereDate()
  7.   PHP Laravel Eloquent join with subquery
  8.   Angularjs convert json string to array
  9.   jQuery Delete Confirm in Laravel 5.7 Example
  10.   Laravel Database Eloquent Unions Query