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.

Read Also:  how to remove query string from url?

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.

Read Also:  Eloquent Right Join Queries in Laravel Example

$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. Read Also:  PHP Laravel Eloquent join with subquery
  2. Read Also:  ng-table using Angular Example
  3. Read Also:  Laravel 6 Advanced Where Clauses Tutorial With Example
  4. Read Also:  jQuery Get the Text Value of Selected Option
  5. Read Also:  Laravel redirect previous page after login
  6. Read Also:  Eloquent orwhere Query Use in Laravel
  7. Read Also:  Laravel Nova Minimum Requirements
  8. Read Also:  Vuejs Timing Events setTimeout Example
  9. Read Also:  Form Data Binding using Angular Example
  10. Read Also:  Laravel Advance Database Query Builder
CLOSEX