Query builder in laravel 8
Laravel query builder use PDO parameter bindings but PDO does not support binding column name.
Retrieve all results form a table:
$users=DB::table('users')->get(); return $users;
Retrieve all result of specific column name
$users=DB::table('users')->pluck('name'); return $users;
Using where clause to get all result:
$users=DB::table('users')->where('name',"Navid")->get(); return $users;
Retrieve a Single Row from a table:
$users=DB::table('users')->first(); return $users;
Retrieve a single row with where clause:
$users=DB::table('users')->where('name',"Navid")->first(); return $users;
Retrieve a single row by its id:
$users=DB::table('users')->find(1); return $users;
Chunking Result:
If we have thousand of result, we may want to get 100 records each time
DB::table(‘users’)->orderBy(‘id’)->chunk(100,function($users){ foreach($users as $user){ }});
We may strop further chunks from going forward using returning false
DB::table(‘users’)->orderBy(‘id’)->chunk(100,function($users){ foreach($users as $user){ return false; }});
If update and chunk are occurring at the same time it may cause some unexpected behavior.If we plan to update the record while chunk, we should use chunkbyid
Lazy Method:
Lazy Method returns the result as chunk but we can interact the result one by one.
DB::table(‘users’)->orderby(‘id’)->lazy()->each(function ($user){ });
If we plan to update the record using Lazy Method, we should use LazyByID
DB::table(‘users’)->where(‘name’,”navid”) ->LazyByID()->each(function($user){ });
Aggregates:
Query Builder also support count, max, min, avg and sum.
DB::table(‘users’)->count(‘id’); DB::table('users')->max('id');DB::table(‘users’)->min(‘id’);DB::table(‘users’)->avg(‘price’);DB::table(‘user’)->sum(‘price’);
Find the record if exist or doesntExist. It will return true or false
$user= DB::table('users')->where('name','Hri')->exists();$user= DB::table('users')->where('name','Hri')-> doesntExist();
Select statement:
$user=DB::table('users') ->select('name','id') ->get();
Getting Distinct Value:
DB::table('users') ->distinct() ->get();
Adding additional column:
$firstQuery=DB::table('users')->select('name'); $querytwo=$firstQuery->addSelect('id')->get(); return $querytwo;
We can sent the string using DB::raw.
$string='count(*) as id, name'; $users = DB::table('users') ->select(DB::raw($string)) ->where('name', '=', "Hri") ->groupBy('name') ->get();
Note: Raw statements will be added as strings, so we should avoid creating SQL injection vulnerabilities.
Also check out the other blog ..
For more info:https://laravel.com/docs/8.x/queries