Level 122
you can improve it by only selecting the columns you need for calculations
you can improve it by caching the calculated data so you only need query all data once and not for every page
1 like
Dec 13, 2023
6
Level 8
How to prevent double queries when I need both pagination and the entire collection?
Hello,
I need both paginate for the display but also the entire collection to do some calculations in the backend. Right now I do it straightforward
$data = DB::table('table')->where(...)->get();
$paginator = DB::table('table')->where(...)->paginate();
But can I improve it and prevent double queries? WIll I need to create a custom paginator?
Thanks
Level 8
I have a concrete example now, so maybe there is another solution? I have in my controller the query that paginates:
public function some_controller_method(Request $request)
{
$paginated_data = DB::table('table')->select('id', 'name', 'email', 'unit')->paginate();
return view('some_view', ['data' => $paginated_data])
}
Now I also need all the units available in order to display them inside a <select> dropdown so that the users can filter the results in the frontend:
<select>
@foreach($units as $unit)
<option value="{{ $unit }}">
{{ $unit }}
</option>
@endforeach
</select>
do I have to do another query in this case (Or do one of your suggested solutions) to get all the available units from the table? Or there's another option here?
Level 122
yes, another query
1 like
Level 8
@Snapey thanks. Sometimes that can big a large query that can take long time. In this case you think the only solution for that is in the DB side? i.e. create some temporary table/view to hold the information?
Level 122
@Ligonsker why should it take a long time?
Use Laravel debugbar and measure it
if you are fetching thousands of rows then this is not compatible with a select dropdown
1 like
Level 8
@Snapey yep it might be a lot. you are right I will need to change something there
Please or to participate in this conversation.