Get number of rows in Laravel's Eloquent before using "take" and "skip"

Question

I want to query my Laravel model using eloquent for results that may need to match some where clauses, then take and skip predefined numbers.

This isn't 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 could be every row in the table if no where clauses are used or a few if either is used.

What I want to do could be accomplished by making the query twice, with the first omitting "->take($iDisplayLength)->skip($iDisplayStart)" at the end and counting that, but that 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 on the same query.

And by the way, your whole query is a bit over complicated. It results in something like this:

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

Closure in where is used to make a query like this for example:

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

So to sum up you need this:

$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 splice and a count method which you can take advantage of.

First you would want to get the collection..

$collection = $query->get();

Then you can get the count and splice it.

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

This might be hard on performance because you are querying for the entire collection each time rather than putting a limit on the query, so it might be beneficial to cache the collection if this page is going to be hit often. At the same time though, this will hit the database only once, so it's a bit of a trade off.