How to Paginate Multiple Models in Laravel without overworking the memory

0
votes

Im trying to figure out the best way to paginate in laravel when i am working with big datasets, the method i am using now is overworking the memory. this is what i am doing: i query from multiple tables, map the results etc, then merge multiple collections, and only after that do i paginate it, i realized though that this is a really bad way of going about this, because i am querying way to much data for no reason, and as the data grows the slower it will become. the question is what would be the correct way? i thought maybe i would paginate and then work with the data, the issue is that for instance if i was trying to sort all the merged data by date i wouldn't be getting the proper results because one table may have less data then the other...

here is some of the code to help with clarifying the question

first i will query two tables orders table and transactions table

$transactions = Transaction::all();
$orders = Order::all();

then i will send this all to a action to map it all and format it the way i need to with laravel resources

return $history->execute(['transactions' => $transactions, 'orders' => $orders]);

what this action does is simply map all the transactions and return a resource, then it will map all the orders and return resources for them as well, after which it will merge it all and return the result.

then i take all of this and run it through a pagination action which uses the Illuminate\Pagination\LengthAwarePaginator class.

the results are exactly the way i want them its simply a memory overload.

i tried paginating first instead of doing 

$transactions = Transaction::all();
$orders = Order::all();

i did 

$transactions = Transaction::orderBy('created_at' 'desc' )->paginate($request->PerPage); 
$orders = Order::orderBy('created_at' 'desc' )->paginate($request->PerPage);

and then run it through the action that returns resources. there are a few issues doing this, firstly, i will no longer get back the pagination data so the app won't know the current page or how many records there are, secondly, if for example the orders table has 2 records, and the transactions table has 10 records, event though the dates of the orders table is after those of the transactions table they will still get queried first.

1
laravelpagination
Calling paginate() on a Collection instead of the Builder class (after getting the results from the database) is always going to be inefficient. Databases have a built in pagination logic, in the form of limit and offset, and you can always use joins to map the data, but yeah. Without seeing your code, it would be difficult to assist with this. Also, if you code works, but is inefficient, this might be a better question for codereview.stackexchange.com – Tim Lewis
Your post doesn't give any technical information as to how these models are related, ordered, etc. Therefore my suggestion may not work. That said, have you tried using a UNION query with OFFSET and LIMIT clauses, and to manually instantiate the Paginator instance based on the total count of results? – fubar
@TimLewis i updated the question so you can see a bit more of the code, i cant use pagination on the query for a few reasons, firstly because the results wont be in the proper order when merging them together (if we are sorting by date, then say one table has less records it will get queried before the other tables pagination gets to that date) – Mika
Did you find a reasonable solution to this? I have the same question too. Merging 6 different models into the one collection with the chance of having thousands of total records. – zuc0001

1 Answers

-1
votes

Use chunk method or paginate Read documentation.