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 ..

Http request In Laravel and the form data

 

For more info:https://laravel.com/docs/8.x/queries