How to achieve best performance if I need both pagination and do collection methods on the full result set?

Hello, I think I had a similar question but this time it's with a different method:

I have a pagination:

$pagination = Model::paginate('300');

But I also need to do more actions on the result, but the full result, not paginated:

$result->groupBy('column')->count();

But I don't have the full collection $result, only the paginated so in the case above it would only count the first 300 rows.

What would be the best to do in this case? I can do another query completely but maybe I could do it without an extra query?

idew

3 years ago

Level 26

If you're concerned about performance, doing another query is really your only option. Otherwise, you'd have to load all of your results into memory. SQL is much better at handling large datasets than PHP. Extra queries should be avoided in loops and in cases where a single query can do the job (there are even some exceptions to this) but this is an instance where an extra query is the right choice.

1 like

jlrdw

3 years ago

Level 75

You would be best to let the database do the heavy work and forget about collections.

Some of these examples that have a few hundred work great but you do not want to put tens of thousands of database records into a collection

2 likes

Tray2

3 years ago

Level 73

I agree with both @idew and @jlrdw, don't ever do anything php side that can be done by the database.

2 likes

Please or to participate in this conversation.