How We End Laravel Query Builder Queries?

After using where(), whereIn() or any other methods like when(), select(), groupBy(), orderBy() we need to end Laravel Queury builder query with a method. Query results depends upon that. Here are couple of ways to end Laravel Query Builder queries.

First we have get(). It returns array of records always even if we have one record or none. If one or more records are selected by query then get() will return array of collections.

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

It is compulsory to end Laravel query builder with some kind of ending method like get(). Otherwise we will not get records.

Another set of methods are first() and firstOrFail(). It will always return one collection or null as its quite apparent from its name. It is actually Query builder interpretation of “limit 1”. Lets check its query.

$lead = DB::table( 'leads' )
	->where( 'city', "Orlando" )
        ->first(); 

SQL interpretation of above query will be

select * from `leads` where `city1` = Orlando limit 1

I have a tricky way to get Query builder queries.  You will say we can use toSQL() method. But actually we can not in this case.

$lead = DB::table( 'leads' )
	->where( 'city', "Orlando" )
        ->first()->toSql();

This will result an error. Why because we can not use toSQL() chaining after first() method. But if we will use this query.

$lead = DB::table( 'leads' )
	->where( 'city', "Orlando" )
	->toSql();

Then clearly it will not have limit 1 in that. SQL query will look like this.

"select * from `leads` where `city` = 'Orlando'"

So how to print these kind of queries where we are unable to use toSQL(). I try something as a hack to do things on my end. Let me share that with you guys. I will introduce a bug in my query. For example, I know we have “city” column in “Leads” table but I will intentionally write a query with “city_test” column. It will result an error in browser with SQL query in that. So if I will run this query.

$lead = DB::table( 'leads' )
	->where( 'city_test', "Orlando" )
        ->first();

This will result into this error.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'city_test' in 
'where clause' 
(SQL: select * from `leads` where `city_test` = Orlando limit 1)

Now if we will watch closely we have our query in there.

select * from `leads` where `city` = Orlando limit 1

You can use this anywhere to check SQL query for any query builder query.

Now first() method will silently fail or return null if unable to select any record but firstOrFail() will throw an exception.

Next method is find(). It takes ID as parameter and finds it in our table.

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

Next method value() is same as find() or first() method but it will return value of one column which we will pass as parameter.

$lead = DB::table('leads')
->orderBy('created_at', 'desc')
->value('email');

Next method count() will give us number of records which are selected under that query.

$lead = DB::table( 'leads' )
->orderBy( 'created_at', 'desc' )
->count();

min() and max() methods at the end of the query will return the record with minimum or maximum value possessing regarding to parameter which we will pass.

$leadWithMaxAge = DB::table( 'leads' )->max('age');
$leadWithMinAge = DB::table( 'leads' )->min('age');

avg() will return average value of column which we will pass as parameter.

$leadsAvgAge = DB::table( 'leads' )->avg('age');

sum() will give use total sum of values on the bases of column mentioned in parameters.

$sumOfAges = DB::table( 'leads' )->sum('age');

If you have any questions regarding ending query builder queries then please leave a comment in comments section.