I am developing an admin panel for my personal project. I have an index page, where all products from the database will be put inside a paginated DataTable. Right now the database consists of 10 000+ products and when I open the index page it freezes until all the products are loaded,after which the pagination, search bar, "entities per page" is applied.
The index.blade.php:
<!DOCTYPE html>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
@extends('admin.layout.layout')
@section('content')
@if(Session::has('success_message'))
<div class="alert alert-success">
<span class="glyphicon glyphicon-ok"></span>
{!! session('success_message') !!}
<button type="button" class="close" data-dismiss="alert" aria-label="close">
<span aria-hidden="true">×</span>
</button>
</div>
@endif
<div class="card">
<header class="card-header clearfix">
<div class="pull-left">
<h4>Products</h4>
</div>
<div class="btn-group btn-group-sm pull-right" role="group">
<a href="{{ route('admin.product.create') }}" class="btn btn-success" title="Create New Product">
<i class="fa fa-plus-square" aria-hidden="true"></i>
</a>
</div>
</header>
@if(count($products) == 0)
<div class="card-body text-center">
<h6>No products Available.</h6>
</div>
@else
<div class="card-body">
<div class="adv-table">
<table class="display table table-bordered table-striped" id="dynamic-table" style="table-layout: fixed;">
<thead>
<tr>
<th>Title</th>
<th class="text-center">Image</th>
<th class="text-center">Product code</th>
<th class="text-center">Price</th>
<th class="text-center">Active</th>
<th class="text-center">In promotion</th>
<th class="text-center">Promotion price</th>
<th class="text-center">Edit/Delete</th>
</tr>
</thead>
<tbody>
@foreach($products as $product)
<tr>
@if($product->title)
<td class="align-middle truncate text-center">{{ $product->title }}</td>
@else
<td class="align-middle truncate text-center"><span> - </span></td>
@endif
@if($product->image)
<td class="align-middle truncate text-center"><img src="{{ asset( $path . $product->image )}}" alt="{{ $product->title }}" class="img-thumbnail" width="70" height="70"></td>
@else
<td class="align-middle truncate text-center"><span> No image </span></td>
@endif
<td class="align-middle truncate text-center"><span> {{ $product->product_code }} </span></td>
<td class="align-middle truncate text-center"><span> {{ $product->price }} </span></td>
<td class="align-middle truncate text-center">{{ ($product->is_active) ? 'Yes' : 'No' }}</td>
<td class="align-middle truncate text-center">{{ ($product->is_promotion) ? 'Yes' : 'No' }}</td>
@if($product->promotion_price)
<td class="align-middle truncate text-center"><span> {{ $product->promotion_price }} </span></td>
@else
<td class="align-middle truncate text-center"><span> - </span></td>
@endif
<td class="align-middle truncate text-center">
<form method="POST" action="{!! route('admin.product.destroy', $product->id) !!}" accept-charset="UTF-8">
<input name="_method" value="DELETE" type="hidden">
{{ csrf_field() }}
<div class="btn-group btn-group-xs" role="group">
<a href="{{ route('admin.product.edit', $product->id ) }}" class="btn btn-primary tooltips btn-sm" title="Edit Product" data-toggle="tooltip" data-placement="bottom" data-original-title="Tooltip on bottom">
<i class="fa fa-pencil-square-o" aria-hidden="true"></i>
</a>
@if(Auth::user()->user_role === 'admin')
<div class="btn-group btn-group-xs" role="group">
<button type="submit" class="btn btn-danger tooltips btn-sm" title="Delete Product" onclick="return confirm("Click Ok to delete Product.")" data-toggle="tooltip" data-placement="bottom" data-original-title="Tooltip on bottom">
<i class="fa fa-trash-o" aria-hidden="true"></i>
</button>
</div>
@endif
</form>
</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
@endif
</div>
@stop
@section('js')
<!--dynamic table initialization -->
@endsection
I searched through the internet for some solutions of this problem and found out there is an option to fix this, using an ajax to lazy load the entities. I tried calling the constructor $('#dynamic-table').DataTable({}) to my table and add the necessary properties, but I guess I miss something, since on page load still all the entities are loaded. I added some console.log statements inside the ajax for debugging purposes and none of the lines is getting printed, making me think that it is not even executed. I would be really happy if anyone could give me some hints or guidelines.
The javascript I added to the page:
<script>
$(document).ready(function() {
$('#dynamic-table').DataTable({
serverSide: true,
ajax: {
url: '{{ route("admin.product.getproducts") }}',
type: 'GET',
headers: {
'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
},
success: function(data) {
console.log('AJAX Success:', data); // Log the AJAX response
},
error: function(xhr, textStatus, errorThrown) {
console.log('AJAX Error:', errorThrown); // Log any AJAX errors
}
},
columns: [{
data: 'title',
name: 'title'
},
{
data: 'image',
name: 'image',
orderable: false,
searchable: false
},
{
data: 'product_code',
name: 'product_code'
},
{
data: 'price',
name: 'price'
},
{
data: 'is_active',
name: 'is_active'
},
{
data: 'is_promotion',
name: 'is_promotion'
},
{
data: 'promotion_price',
name: 'promotion_price'
},
{
data: 'actions',
name: 'actions',
orderable: false,
searchable: false,
render: function(data, type, full, meta) {
var html = '<a href="' + full.edit_url + '" class="btn btn-primary tooltips btn-sm" title="Edit Product" data-toggle="tooltip" data-placement="bottom" data-original-title="Tooltip on bottom">' +
'<i class="fa fa-pencil-square-o" aria-hidden="true"></i></a>';
if (full.user_role === 'admin') {
html += '<button type="button" class="btn btn-danger tooltips btn-sm" title="Delete Product" onclick="confirmDelete(' + full.id + ')" data-toggle="tooltip" data-placement="bottom" data-original-title="Tooltip on bottom">' +
'<i class="fa fa-trash-o" aria-hidden="true"></i></button>';
}
return html;
}
},
],
});
});
function confirmDelete(productId) {
if (confirm("Click Ok to delete Product.")) {
var form = document.createElement("form");
form.method = "POST";
form.action = "{{ url('admin/product') }}" + '/' + productId;
var csrfToken = document.querySelector('meta[name="csrf-token"]').getAttribute('content');
var hiddenField = document.createElement('input');
hiddenField.type = 'hidden';
hiddenField.name = '_token';
hiddenField.value = csrfToken;
form.appendChild(hiddenField);
var methodField = document.createElement('input');
methodField.type = 'hidden';
methodField.name = '_method';
methodField.value = 'DELETE';
form.appendChild(methodField);
document.body.appendChild(form);
form.submit();
}
}
</script>
The base layout:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="">
<link rel="shortcut icon" href="/ad_asset/img/favicon.html">
<meta name="csrf-token" content="{{ csrf_token() }}">
<title>@yield('title')</title>
@include('admin.include.css')
</head>
<body>
<style>
.invalid-feedback{
display: block;
width: 100%;
margin-top: .25rem;
font-size: 80%;
color: #dc3545;
}
</style>
<section id="container">
@include('admin.include.header')
@include('admin.include.sidebarmenu')
<!--main content start-->
<section id="main-content">
<section class="wrapper">
@yield('content')
</section>
</section>
<!--main content end-->
{{-- @include('admin.include.footer') --}}
</section>
@include('admin.include.js')
@yield('js')
<!--select2-->
<script type="text/javascript">
$(document).ready(function () {
$(".js-example-basic-single").select2();
$(".js-example-basic-multiple").select2();
$("#categories").select2({
placeholder: " Select category",
allowClear: true
});
tinymce.remove('#forme');
});
</script>
</body>
</html>
Js include file:
<script src="{{ asset('/ad_asset/js/jquery.js') }}"></script>
<script src="{{ asset('/ad_asset/js/jquery-ui-1.9.2.custom.min.js') }}"></script>
<script src="{{ asset('/ad_asset/js/bootstrap.bundle.min.js') }}"></script>
<script class="include" type="text/javascript" src="{{ asset('/ad_asset/js/jquery.dcjqaccordion.2.7.js') }}"></script>
<script src="{{ asset('/ad_asset/js/jquery.scrollTo.min.js') }}"></script>
<script src="{{ asset('/ad_asset/js/jquery.nicescroll.js') }}" type="text/javascript"></script>
<script type="text/javascript" language="javascript" src="{{ asset('/ad_asset/assets/advanced-datatable/media/js/jquery.dataTables.js') }}"></script>
<script type="text/javascript" src="{{ asset('/ad_asset/assets/data-tables/DT_bootstrap.js') }}"></script>
<script src="{{ asset('/ad_asset/js/respond.min.js') }}" ></script>
<!--right slidebar-->
<script src="{{ asset('/ad_asset/js/slidebars.min.js') }}"></script>
<!--common script for all pages-->
<script src="{{ asset('/ad_asset/js/common-scripts.js') }}"></script>
<script type="text/javascript" src="{{ asset('/ad_asset/assets/select2/js/select2.min.js') }}"></script>
<script src="{{ asset('/js/axios.js') }}"></script>
<script src="https://cdn.tiny.cloud/1/no-api-key/tinymce/5/tinymce.min.js" referrerpolicy="origin"></script>
The route method:
public function getProducts(Request $request)
{
$columns = ['title', 'image', 'product_code', 'price', 'is_active', 'is_promotion', 'promotion_price'];
$totalProducts = Product::count();
$query = Product::with(['category']);
// Filtering
if ($request->has('search') && $request->input('search.value') != '') {
$searchValue = $request->input('search.value');
$query->where(function ($subquery) use ($columns, $searchValue) {
foreach ($columns as $column) {
$subquery->orWhere($column, 'like', '%' . $searchValue . '%');
}
});
}
// Ordering
if ($request->has('order')) {
$orderColumn = $columns[$request->input('order.0.column')];
$orderDirection = $request->input('order.0.dir');
$query->orderBy($orderColumn, $orderDirection);
}
// Pagination
$start = $request->input('start', 0);
$length = $request->input('length', 10);
$products = $query->skip($start)->take($length)->get();
// Transform data to match DataTables' expected JSON format
$data = [];
foreach ($products as $product) {
$data[] = [
'title' => $product->title,
'image' => $product->image,
'product_code' => $product->product_code,
'price' => $product->price,
'is_active' => $product->is_active ? 'Yes' : 'No',
'is_promotion' => $product->is_promotion ? 'Yes' : 'No',
'promotion_price' => $product->promotion_price,
'edit_url' => route('admin.product.edit', $product->id),
'user_role' => 'moderator',
'id' => $product->id,
];
}
return response()->json([
'draw' => $request->input('draw'),
'recordsTotal' => $totalProducts,
'recordsFiltered' => $query->count(),
'data' => $data,
]);
}
The index method:
public function index()
{
$products = Product::with(['category'])->get(); // 10 000+ entities
$path = 'images/products/';
return view('admin.product.index', compact('products','path'));
}
