Gareth Harding Gareth Harding - 19 days ago 5
PHP Question

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

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

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();