I'm confused, I thought serverSide would paginate and prevent all records from being loaded at once?

FoxRocks76

I'm converting my table from client side to server side, and when I run a count on the data being returned, it's always the total number of records. I'm using the Yajra Laravel package, so it looks a bit different, but I don't see anything on that package that makes me believe it's having any effect. Please take a look and let me know if there's anything glaringly obvious

Here is my Controller:

public function getPurchaseOrders(Request $request, PurchaseOrder $purchase_orders)
    {
        if ($request->ajax()) {
            $query = $purchase_orders->withTrashed()->orderBy('purchase_order_number', 'desc')->with('vendor');
            $searchValue = strtolower(trim($request->input('search.value')));

            if ($searchValue === 'open') {
                $query->where('status', '1');
            } 
            elseif ($searchValue === 'closed') {
                $query->where('status', '0');
            } 
            elseif ($searchValue === 'deleted') {
                $query->whereNotNull('deleted_by');
            }

            // Get the total number of records before pagination
            $totalRecords = $query->count();

            $data = $query->get();
            
            Log::info($data->count());

            // Transform the status column value based on the search value
            $data->transform(function ($item) use ($searchValue) {
                if ($searchValue === 'open') {
                    $item->status = '<span class="redEx">Open</span>';
                } 
                elseif ($searchValue === 'closed') {
                    $item->status = '<span class="greenCheck">Closed</span>';
                } 
                elseif ($searchValue === 'deleted') {
                    $item->status = '<span style="color: grey;">Deleted</span>';
                } 
                else {
                    $item->status = PamsHelper::booleanToOpenClosedDeleted($item->status, $item->deleted_at);
                }
                return $item;
            });

            return DataTables::of($data)
                ->addColumn('unique_number', function ($data) {
                    return $data->unique_number;
                })
                ->addColumn('vendor', function ($data) {
                    return $data->vendor->name;
                })
                ->addColumn('created_at', function ($data) {
                    return $data->created_at->format('Y-m-d');
                })
                ->addColumn('date_expected', function ($data) {
                    return $data->date_expected->format('Y-m-d');
                })
                ->addColumn('status', function ($data) {
                    return $data->status;
                })
                ->addColumn('action', function ($data) {
                    return '<a href="'.action('PurchaseOrderController@show', $data->unique_number).'" style="margin-left: 3px; color: #7cd157;" title="Show Purchase Order"><span class="glyphicon glyphicon-eye-open"></span></a>
                            <a href="'.action('PurchaseOrderController@edit', $data->unique_number).'" style="margin-left: 3px; color: #5BC0DE;" title="Edit Purchase Order"><span class="glyphicon glyphicon-edit"></span></a>';
                })
                ->with([
                    'recordsTotal' => $totalRecords, // Total records before pagination
                    'recordsFiltered' => $totalRecords, // Total records after applying filters
                ])
                ->make(true);
        }
    }

Here is the Javascript in my view:

<script>
    $(document).ready(function () {
        $('#purchase_orders_table').DataTable({
            processing: true,
            serverSide: true,
            pageLength: 10,
            ajax: "{{ route('purchase-orders.list') }}",
            dom: '<"dt-search-box"f><"dt-loader-wrapper"l><"dt-table-wrapper"t><"dt-info-wrapper"i><"dt-pager-wrapper"p>',
            language: {
                loading: '<div class="dt-loader"><span class="bounce1"></span><span class="bounce2"></span><span class="bounce3"></span></div>'
            },
            columns: [
                { data: 'unique_number', name: 'unique_number', className: 'text-center', orderable: false },
                { data: 'vendor', name: 'vendor.name' },
                { data: 'created_at', name: 'created_at', className: 'text-center' },
                { data: 'date_expected', name: 'date_expected', className: 'text-center' },
                { data: 'status', name: 'status', className: 'text-center' },
                { data: 'action', name: 'action', className: 'text-center' },
            ],
            columnDefs: [
            { width: '10%', targets: 0 },
            { width: '40%', targets: 1 },
            { width: '15%', targets: 2 },
            { width: '15%', targets: 3 },
            { width: '10%', targets: 4 },
            { width: '10%', targets: 5 }
            ],
            search: {
                search: 'Open'
            }
        });
    });
</script>

As you can see I'm checking to see how many results are being retrieved and logging it, and from that log I can see that it's always the full number of records, which I think sort of defeats the purpose of using server side.

Thanks for your time

colin

It looks like you're not including draw in the response, this needs to match the same value in the request, as that's how DataTables knows that's the correct response.

The protocol is discussed here. Also see examples here.

Colin

kthorngren

I'm not familiar with Laravel nor Yarja Datatables but based on what I've seen in tutorials the tutorial functions to fetch the data doesn't have the queries. I believe Yarja Datatables relies on using models to access the database and would have all the query functionality built in to limit the number of records returned. It doesn't look like your above query limits the number of records.

My assumptions might be incorrect but I recommend looking at the online Yarja tutorials for the Laravel version you have. Yarja Datatables is developed by a third party. Questions about the package should be directed to the developer because there isn't expertise with that package here.

Kevin