Get the number of lines in Laravel Eloquent before using take and skip.

Question

I want to query my Laravel model using eloquence for the results that might be required to match where clauses, and then take and skip predefined numbers.

This is not a problem in itself, but I also need to know the number of rows that were found in the query before reducing the result set with take and skip - so the original number of matches, which can be each row in the table, if any either offers or several if used.

What I want to do can be done by executing the request twice, with the first pass " ->take($iDisplayLength)->skip($iDisplayStart) " at the end and counting it, but it just seems messy.

Any thoughts?

 $contacts = Contact::where(function($query) use ($request) { if (!empty($request['firstname'])) { $query->where(function($query) use ($request) { $query->where('firstname', 'LIKE', "%{$request['firstname']}%"); }); } if (!empty($request['lastname'])) { $query->where(function($query) use ($request) { $query->where('lastname', 'LIKE', "%{$request['lastname']}%"); }); } }) ->take($iDisplayLength)->skip($iDisplayStart)->get(); $iTotalRecords = count($contacts); 

Answer 1

You can use count , then get in the same query.

And by the way, your entire request is a little more complicated. This leads to something like this:

 select * from `contacts` where ((`firstname` like ?) and (`lastname` like ?)) limit X, Y 

Closing in where used to create such a request, for example:

 select * from table where (X or Y) and (A or B); 

So to summarize:

 $query = Contact::query(); if (!empty($request['firstname'])) { $query->where('firstname', 'like', "%{$request['firstname']}%"); } if (!empty($request['lastname'])) { $query->where('lastname', 'like', "%{$request['lastname']}%"); } $count = $query->count(); $contacts = $query->take($iDisplayLength)->skip(iDisplayStart)->get(); 

Answer 2

The Collection class offers a splicing and counting method that you can use.

First you would like to get a collection.

 $collection = $query->get(); 

Then you can get the counter and spliced ​​it.

 $count = $collection->count(); $records = $collection->splice($iDisplayStart, $iDisplayLength); 

This can be difficult for performance because you request the entire collection every time, rather than setting a limit on the request, so it may be useful to cache the collection if this page is hit frequently. At the same time, this will only hit the database once, so this is a bit of a compromise.