Laravel Query Builder Select, Join and Where

Query Builder in Laravel, from the Scratch

Query Builder in Larevel is a Package through which we create and run database query Quickly to run the application smoothly. Query Bulider Use PHP Data Object (PDO) where we dont have to worry about the SQL Injection. It can perform all Database Operation such as CRUD, DB Connection, Aggregate Function etc.

We will apply all the Laravel Query Builder Concept on the personal_detail table. Create Table Named With personal_detail,   

CREATE TABLE `personal_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(300) DEFAULT NULL,
  `dob` date DEFAULT NULL,

  `marks` int(7) DEFAULT NULL,

  `statusflag` int(7) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41092 DEFAULT CHARSET=latin1

Insert Data in personal_detail Table.

⧉  Query to Fetch / Retrieve using get() method all the data from personal_detail Table, We use get() method to do that.

$fetchData = DB::table('personal_detail ')->get();

 ⧉  Query to Fetch using first() method single row from personal_detail Table, We use first() method to do that.

$fetchData = DB::table('personal_detail ')->where('id','=',4)->first();

 ⧉  Use value() method to get single value from the table record if you don't need the entire row from personal_detail Table, We use value() method to do that.

$fetchData = DB::table('personal_detail ') 

->where('id','=',4) 

->value('student_name');

⧉  Use find() method to get the particular row using ID Column.

$fetchData = DB::table('personal_detail ')->find(5);

⧉  Use pluck() method to get the list of required column from the personal_detail Table.

$fetchData = DB::table('personal_detail ')->pluck('student_name');

Laravel Query Builder Aggregate Function

count(), max(), min(), sum(), avg() is aggregate method in Query Builder.

⧉  Use below Query to uderstan the aggregate function usage in Query Bulider.

$fetchData = DB::table('personal_detail ')->max('marks');

$fetchData = DB::table('personal_detail ')->min('marks'); 

$fetchData = DB::table('personal_detail ')->count();

$fetchData = DB::table('personal_detail ')->avg('marks');

 Laravel Query Builder SELECT

Sometime we want to multiple Column from the particular table, at that time we use select() method 

$fetchData = DB::table('personal_detail ')

->select('student_name','dob')->get();

$fetchData = DB::table('personal_detail ') 

->select('student_name','marks') 

->where('marks','>',50) 

->get();

Distinct() method in Query Builder

$fetchData = personal_detail::distinct()->select('marks') 

->where('marks', '>', 30)

->get();

Laravel Query Builder WHERE Clause

In where() method we pass three parameter, First Parameter is Table Column Name, Second Column Name contain the operator through which we want to compare (Comparison Operator supported by Database ) and third one is the value.

Suntax for where() method:

where('column-name',' Comparision operator', value)

$fetchData = DB::table('personal_detail ')->where('id','=',4)->first();

We can use multiple where Clause in the same query.

$fetchData = DB::table('personal_detail ')->where('id','>',4)

->where('marks','>',60)

 ->get();

you can also pass the array of condition in where Clause. Below is the example.

$fetchData = DB::table('personal_detail')

->where([['marks', '>', 30], ['marks','<',80]]) 

->orderby('marks', 'DESC')->get();

orWhere() method in Laravel Query Builder

orWhere() method is like or Statement. See the below query to understand the concept.

$fetchData=DB::table('personal_detail')->where('marks','>',60) 

->orWhere('id','>',6)

->get();

$fetchData=DB::table('personal_detail')->where('id','>',4) 

->orWhere(function($query){
$query->where('marks','>',60)
->where('student_name','=','NAVEENKUMAR')
})
->get();

whereBetween() method /  orWhereBetween()

$fetchData=DB::table('personal_detail')->whereBetween('id',[2,8])->get();