How to Use Query Builder in Laravel For SQL Complex Queries?

Laravel query builder is the single source to interact with database no matter its SQL, sqlLite, postgresql or mongodb. Laravel allows query builder to interact with database irrespective of database we are using behind the scene. Lets test and build some more queries using query builder.

This is the query for “where” with “between” clause.

leads = DB::table( 'leads' )
->whereBetween( 'age', [ 20, 30 ] )
 ->get();

This the query for ‘where’ with ‘in’ clause.

$leads = DB::table( 'leads' )
->whereIn( 'state', [ 'LA', 'GA', 'AL' ] )
->get();

And this is the query for ‘where’ with ‘IS NULL’ clause.

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

And this is query for ‘where’ and ‘is not null’ clause.

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

We can place raw SQL as parameter to whereRaw method like this.

$leads = DB::table('leads')
->whereRaw('city = "New York"')
->get();

Please remember that parameters of “whereRaw” method will not be escaped so we must use this method very carefully. Otherwise this may lead to SQL injection.

$leads = DB::table( 'leads' )->whereExists( function ( $query ) {
     $query->select( 'id' )
     ->from( 'meta' )
     ->whereRaw( 'meta.lead_id = leads.id' );
} )->get();

Now we can use this query to get distinct values from some columns in our database.

$cities = DB::table( 'leads' )
          ->select( 'city' )
          ->distinct()
	  ->get();

If we want to order our records with respect to values in some columns in database then we can use this query.

$leads = DB::table( 'leads' )
		           ->orderBy( 'created_at', 'desc' )
		           ->get();
$leads = DB::table( 'leads' )
		           ->orderByDesc( 'created_at' )
		           ->get();
$leads = DB::table( 'leads' )
		           ->orderByRaw( 'leads.created_at' )
		           ->get();

Again use orderByRaw() very carefully.

These are queries for “group by” and “having” clause.

$leads = DB::table('leads')
	    ->select('city', 'state')
	    ->groupBy('city', 'state')
	     ->get();

$leads = DB::table('leads')
           ->select('age')
           ->groupBy('age')
           ->having('age', '>', 20)
           ->get();

$leads = DB::table('leads')
           ->select('age')
           ->groupBy('age')
           ->havingRaw('age > 20')
           ->get();

Here is the query to use skip() and take()

$leads = DB::table('leads')->skip(10)->take(5)->get();

latest() and oldest() just sort our records on the bases of “created_at” if no column passed but if there are any columns then they sort on the bases of parameters.

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

inRandomOrder() just return records in a random order.

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

I hope now you understand everything about Laravel query builder and how it works. It is very fun skill if you understand it perfectly. You will definitely have some questions about it. Please leave a comment. Even if you are working on a project and having a problem regarding query builder. We appreciate your contributions and questions.