Hide/Show Bootstrap Table Columns When Using Laravel Pagination

Hi :)

I am working on a Laravel project where I have a table containing tens of thousands of records, which I must display to the user in a blade view (in a table).

Currently, I am using a function in the controller to get all records ($devices) from the table (devices table) using Laravel's pagination, as shown below:

In the controller:

public function index()
{
    $devices = Device::paginate(50);
    return view('devices', [
    	'devices'=>$devices,
     ]);
}

In my devices.blade.php view, I am then displaying all rows and columns in a bootstrapTable, as shown below:

@if(count($devices))
			{{-- Show all devices table --}}
			<div 
				id="toolbar">
			</div>
			<table id="table" class="table table-responsive table-theme v-middle" data-plugin="bootstrapTable" data-toolbar="#toolbar" data-search="false" data-search-align="left" data-show-export="false" data-show-columns="true" data-detail-view="false" data-mobile-responsive="true"
			data-pagination="false" data-page-list="[10, 25, 50, 100, ALL]">
				<thead>
					<tr>
						<th data-field="id">ID</th>
						<th data-field="code">Device Code</th>
						<th data-field="comments">Comments</th>
						
						Other fields...

					</tr>
				</thead>
				{{-- Table content --}}
				<tbody>
					@foreach ($devices as $device)
						<tr class=" " data-id=" ">
							{{-- <td style="min-width:30px;text-align:center"> --}}
							<td class="flex">
								{{ $device->id }}
							</td>
							<td class="flex">
								{{ $device->code }}
							</td>
							<td class="flex">
								{{ $device->comments }}
							</td>
							
							Other fields...

						</tr>
					@endforeach
				</tbody>
			</table>
			<div>
				{{ $devices->links() }}
			</div>
		@else
			<div class="row row-sm sr">
	    		<p>Sorry, but nothing matched your search terms. Please try again with some different keywords.</p>
			</div>
		@endif

The issues I am encountering are the following:

Since I'm using Laravel's pagination feature to deal with the large quantity of data which I need to display, Bootstrap table features such as data-search, data-show-columns and data-sortable only work for the table on the current page. This means that when using the bootstrap table's search bar, you would only be searching in the current page and not in all records.
Similarly, when sorting the fields in ascending/descending order, the sorting is only applied to the 10/25/50/100 records currently being displayed and not to the entire list of records (even those which are in other pagination pages).
With regards to show/hide columns, when I click on the next page button (the one added when using Laravel Pagination, to go from mywebiste/devices/ to mywebiste/devices/?page=2 (the second page of the paginated results), all columns are displayed again, even if some were hidden in the previous page.
'Passing' all records (thousands) to the view without Laravel's Pagination was not an option due to the large amount of data that needs to be displayed.

What would be the best solution to achieve the required search, sort, filter, hide columns features in the table, while not running into problems related to loading tens of thousands of records from the database please?

Note: I have successfully implemented search and sorting features using a POST form in my devices view and a search/sort method in the DeviceController, however, I still believe there is a better/cleaner and simpler way of achieving these features - And I still haven't managed to do something similar for 'hiding/showing columns' in the table.

Thanks!

bobbybouwmann

5 years ago

Best Answer

Level 88

The best solution here is probably using a frontend solution for your table. You can then implement ajax calls to fetch the data. This way you can search and still have pagination available. The same goes for keeping track of what columns your showing or not. A javascript solution with ajax solves all of this for you.

You can also solve this with Laravel, but that means you need to set sessions or adjust the URL to keep track of the columns and also load all the data on the page.

3 likes

BrianA

5 years ago

Level 1

Hi @bobbybouwmann, thanks for your reply.

Regarding the Laravel solution, in fact, currently for the search and sorting functions, I am adjusting the URL by appending the search 'keyword', sort selection, and sorting order (ascending or descending) to the URL:

In the search and sort function in the DevicesController:

$devices->appends(array('keyword'=>$request->input('keyword')));
$devices->appends(array('sort'=>$request->input('sort')));
$devices->appends(array('order'=>$request->input('order')));

The following is the URL when moving from page 1 of the paginated and tabulated results to page 2:

http://mywebsite/devices/results?keyword=sig&sort=id&order=ASC&page=2

This worked, but then, as discussed in my first post, I got stuck when trying to do the hide/show columns feature. As you said, this would need to adjust the URL and a lot of parameters would have to be added to keep track of the columns and each time load the data which 'belongs' to those parameters.

With regards to the AJAX solution you suggested, would you suggest using Laravel DataTables such as YajraBox?

Thanks for your suggestions/guidance!

bobbybouwmann

5 years ago

Level 88

I would personally build it myself using AJAX requests and probably my own Vue component or basis javascript component.

However, DataTables is a good out of the box solution if that is what you need. That plugin does have all the features you need though ;)

1 like

BrianA

5 years ago

Level 1

Hi,

Sorry for the late reply! Thanks for your opinion. I will probably use DataTables then as it would probably save me some time and seems good for my application. However I would try building my own in the future :)

Thanks again, Brian

Please or to participate in this conversation.