db

How to Use Query Builder in Laravel For Database Queries?

We can use query builder in Laravel for different database queries no matter if its SQL, sqlLite, postgresql or mongodb. Laravel allows the query builder to run queries irrespective of database we want to use at the backend. Let’s start it with this selection first.

There are few ways to write selection query in Laravel.

$lead = DB::select( 'select * from leads where email = "[email protected]"' );

We can write this way too

$email = "[email protected]";
$lead  = DB::select( 'select * from leads where email = ?', [ $email ] );

We can also write this in this fashion to show parameters in better way.

$email = "[email protected]";
$lead  = DB::select( 'select * from leads where email = :email', [ 'email' => $email ] );

Now we can insert in a very simple way.

$lead = [ 'saly', '[email protected]' ];
DB::insert( 'insert into leads (name, email) values (?,?)', $lead );

We can update like this

$email = "[email protected]";
DB::update( 'update leads set email = ? where id = ? ', [ $email, 97 ] );

We can delete a record using this query.

DB::delete( 'delete from leads where id = ?', [ 97 ] );

Now there is also a fluent way to write queries using Laravel Query builder.

$lead = DB::table('leads')->get();

or with where clause.

$lead = DB::table('leads')->where('email', '[email protected]')->get();

now if we want to change index of some column in our selection then we can do that easily. Here I will change email to lead_email.

$leads = DB::table( 'leads' )->select(  'email', 'email as lead_email' )->get();
$leads = DB::table( 'leads' )->select(  'email' )->addSelect('email as lead_email')->get();

Now we can select records with where having some operator as well. Here we are selecting Leads which are created in last 15 days from today. Very easy.

$leads = DB::table( 'leads' )->where( 'created_at', '>', now()->subDay(15) )->get();

Now we can select record with multiple where like this.

$leads = DB::table('leads')->where('city', 'New York')->where('status', 1)->get();

So this selection will have this SQL query.

"select * from `leads` where `city` = 'New York' and `status` = 1"

How we know this? We have a method called toSql(). We can use this method to print SQL query.

$leads = DB::table('leads')->where('city', 'New York')->where('status', 1)->toSql();

Now if we will use where multiple times then it will translate as ‘AND’ in SQL or what if we want to use ‘OR’? We have orWhere() method for this. Here is an example.

$leads = DB::table('leads')->where('city', 'New York')->orWhere('status', 1)->get();

Now if we want to run a complex query like and AND statement within OR. For example,

select * from `leads` where `city` = 'New York' or (`status` = 1 and `created_at` > '01-03-2021')

We can do that using function as parameter.

$leads = DB::table( 'leads' )
          ->where( 'city', 'New York' )
          ->orWhere( function ( $query ) {
	         $query->where( 'status', 1 )
	         ->where( 'created_at', '>', now()->subDay( 15 ) );
		 } )
           ->get();

I hope now you understand everything about Laravel query builder for database queries. Please leave a comment of you have any difficulty regarding Laravel query builder.