[Proposal] allow total records count to be set manually in paginate() method

[brynj-digital]

The paginate() method, used by the Eloquent query and query builder (laravel docs), could benefit from the ability to manually pass the total record count to it, rather than have it calculated by the runPaginationCountQuery() method. I'd suggest as an optional (second?) parameter.

Often, a query that needs to be paginated includes several joins and sub-queries that do not affect the total records, but that can add significant overhead to the time needed to run the count aggregate query - I have a real world example at the moment that is approximately 5 times slower than it needs to be.

Allowing a user to manually calculate the total records with their own simpler query, then pass to the paginate() method seems logical to me.

[sisve]

If you go so far as to execute the count-query yourself, couldn't you just execute both queries and create your own instance of the LengthAwarePaginator?

[danjdewhurst]

I wouldn't say executing your code count query was an extreme step. Also, working with large datasets isn't going to be nice with the LengthAwarePaginator if you have to pass the whole collection (?) to it.

I think what Bryn's suggesting makes sense. I can think of many scenarios where you need the count for various things inside your controller, so it only makes sense to be able to pass it to the paginate function or you're doing the same query (if not the more inefficient query Eloquent generates) twice.

[brynj-digital]

My understanding of LengthAwarePaginator is that it expects to be passed the full collection of items - is that correct? As per API docs.

If that's the case, it would not seem like the correct approach - in fact, it would be even more inefficient (my result set is in the thousands).

[sisve]

The LengthAwarePaginator expects the items of the current page, not all the items.

$totalCount = /* logic goes here */;
$pageItems= $query->forPage($currentPage, $itemsPerPage)->get($columns);
$paginator = new LengthAwarePaginator($pageItems, $totalCount, $itemsPerPage, $currentPage)

[brynj-digital]

Ah, I see - that's really not clear from the description in the docs ($items = 'All of the items being paginated.').

Also, I don't really see what LengthAwarePaginator needs the $items for - if I pass an empty array, I seem to get the same results. Any ideas?

[sisve]

That sounds weird. Are you iterating your paginator (foreach($paginator as $item)) in your view, and still get the items? Or are you only looking at the generated paging links?

[franraknic]

Reviving this issue because my options are running out.
Woking with a database with 3M+ records and returning ~100K+ results from a controller. There is need for the total number of returned rows (that makes simplePaginate not enough) and since LengthAwarePaginator makes a count(*) query along with the request query it makes the response time very slow (the count query by itself takes about 2000ms).
If I get the results with get() and use count($results) this makes it slow because mySQL is returning a large dataset i.e the query is slow since it has no LIMIT and OFFSET.

Is there some pattern or solution in Laravel/Lumen that is used when the datasets are large?

[newk]

One possible scenario is to use the count(*) over that is available in some DBMS (at least postgresql and oracle). With this, it's possible with a single query to know the total number of rows that the WHERE clause originated, along with a LIMIT, OFFSET at your choice.
When the data comes from the DB, you just need to remove the column generated by the count over and assign it to the .