VertexBuffer's avatar

Eloquent GroupBy Pagination?

I'm dealing with an app that has some data I need to sort into groups based on the day they were created, and then be paginated for view on a webpage.

I've gotten the basics working;

$result = Model::query()->oldest()->get()->groupBy(function ($model) {
        return $model->created_at->format('j-M-Y');
});

I've scowered the web for an answer to this, and every single answer I find links the same thing, this message from the Laravel docs;

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

However, this has since been removed from the Laravel Docs but no real explanation or example has really been given about how to use groupBy with pagination from what I've seen.

I was hoping someone might explain this to me? I want to avoid using any sort of RAW SQL if possible, and keep it as much eloquent as possible.

Eg example of desired output

[
    '25-Apr-2022' => [
        Model,
        ...
    ],
    '26-Apr-2022' => [
        Model,
        ...
    ],
    '27-Apr-2022' => [
        Model,
        ...
    ],
    etc...
]
0 likes
3 replies
jlrdw's avatar

Have a look at https://christophersax.com/2016/custom-lengthawarepaginator-in-laravel/

The slice is not good, just see this part:

If you do expect a somewhat larger result set, you should also include a LIMIT and OFFSET option in your SQL query for optimization (and just pass these results without manually slicing the array).

Slice is fine with smaller results, not 10 million. https://laracasts.com/discuss/channels/guides/length-aware-paginator

Generally pagination with grouping is not efficient, as all needs transversed each time.

Group by is better suited for shorter results like summary reports. I normally do the smallest possible, meaning if quarterly would be enough, then an annual is not needed. Just combine the 4 quarters.

1 like
VertexBuffer's avatar

@jlrdw Thanks for the reply. I understand I can make a custom paginator to handle this, as that is what was described in the message that was on the Laravel docs I linked above, but that message has been removed since Laravel 8. If it's removed, I'm assuming the issue was resolved and Eloquent now handles these cases correctly, and if that's the case I'm hoping it will also eliminate the need for a custom paginator like used in the blog post you linked.

jlrdw's avatar
jlrdw
Best Answer
Level 75

@VertexBuffer a lengthaware custom paginator is extremely easy to write. Normally a GROUP BY isn't well suited for pagination. In the guide I linked, you can see there is nothing to it. I haven't combed the source for it, but I know the regular paginate may not handle it well, but I guess I will comb through code, you could do the same starting with the API.

Seems the count code is the same in ver 7 and ver 9.

3 likes

Please or to participate in this conversation.