How to implement a pagination model and make use of specifically formatted JSONs

guineadd · February 2022

Greetings everyone. I have been using DataTables the past few months and it certainly is good to be part of the forum. Please forgive me if the following question has been asked in the past.

Questions

What changes in the table initialization, or in general, can be made in order for the page buttons to properly call the appropriate batch of entries?
How could DataTables' paging feature implement Laravel's or any pagination model?

Issue

I am trying to use a datatable to display info on a webpage that is expected to reach hundreds of thousands of entries at the worst. Because of potential API traffic issues I have been given specialized calls to the local server API that return a JSON in the following format, which is generated by Laravel's paginate method:

{
    "current_page": 1,
    "data": [{...,
    }],
    "first_page_url": "http:\/\/127.0.0.1:8000\/api\/test_history\/fullObj?page=1",
    "from": 1,
    "last_page": 134,
    "last_page_url": "http:\/\/127.0.0.1:8000\/api\/test_history\/fullObj?page=134",
    "links": [{...,
    }],
    "next_page_url": "http:\/\/127.0.0.1:8000\/api\/test_history\/fullObj?page=2",
    "path": "http:\/\/127.0.0.1:8000\/api\/test_history\/fullObj",
    "per_page": 15,
    "prev_page_url": null,
    "to": 15,
    "total": 2000
}

Of the above properties I have managed to utilize "data" and "total". DataTables takes care of the "per_page", "from" and "to" properties. The array "links" contains links to the page buttons with the "url", "label" and "active" properties. My issue is that I cannot make use of the more useful properties, like "next_page_url" or "prev_page_url". Additionally, whenever I am using DataTables' previous/next buttons or any page buttons the same table page is being loaded.

Within the documentation I read that some data needs to be manipulated for the payload and response to coincide a little. So, I made use of the "data" prop to correctly populate the table (with the first 15 entries) and the "total" prop to receive the correct number of buttons in the paginate area of the datatable. Here follows the state of the table initialization:

$(document).ready(function() {
  $('#test_table').DataTable( {
    "order": [[ 8, "desc" ]],
    "scrollX": true,
    "lengthMenu": [[ 5, 15, 25, 100, -1 ], [ 5, 15, 25, 100, "All" ]],
    "pageLength": 15,
    "processing": true,
    "serverSide": true,
    "ajax": function(data, callback, settings) {
      $.get("http://127.0.0.1:8000/api/test_history/fullObj?page=1", {
        limit: data.length,
        offset: data.start
      },
      function(json) {
        callback({
          recordsTotal: json.total,
          recordsFiltered: json.total,
          data: json.data
        });
      });
    },
    "columns": [
      { "data": "id" },
      { "data": "uid" },
      { "data": "dev_type.type" },
      { "data": "registers.id" },
      { "data": "measurements.id" },
      { "data": "created_at" },
      { "data": "updated_at" }
    ]
  });
});

The "ajax" section is alternatively written as follows:

"ajax": {
      "url": "http://127.0.0.1:8000/api/test_history/fullObj?page=1",
      "dataSrc": function(json) {
        json.recordsTotal = json.total;
        json.recordsFiltered = json.total;
        return json.data;
      }
    },

Hence, I cannot yet load another batch of entries on to the datatable without changing the "url" of course. I have tried accessing the table's HTML elements to manipulate attributes and create onclick events that would load the next url, to no avail. Finally, I would like to highlight a couple of points:

Calling for the entire object containing all entry IDs is out of the question, so it is not an issue of what is rendered in the front end, but what can be sent by the server.
I cannot change the API's source files, and so far my search within Laravel's threads and documentation has not borne any fruit. Since a number of frameworks allow for such pagination models, there should be some way of implementation.

I thank you for your time, all the while looking forward to hearing from you!

colin · February 2022

I suspect the Laravel paging would clash with the DataTables - though there is some chat on the web about it, such as here and here.

Otherwise, you could just use standard server-side processing, will only send records to the client/browser as requested. For server-side processing, enable serverSide. The protocol is discussed here. Also see examples here. If you download the DataTables repo, there are examples of the server-side scripts in /examples/server_side/scripts,

Cheers,

Colin

guineadd · February 2022

Dear Colin,

Thank you for your answer. A possible clash between the paginators constitutes a fear of mine as well. As mentioned in the question, "serverSide": true is already enabled, and I do not have significant access to the Laravel side of things.

Is there a way through initialization or another script to send the information for the next page (or another page) of entries - by making use of the properties "next_page_url", etc. of the Laravel JSON?

Best of regards!

colin · February 2022

I'm not familiar with Laravel, I'm afraid, though those links I posted before would be worth looking at if you haven't already.

colin

guineadd · February 2022

Dear Colin,

You were definitely right the first time around by suggesting a clash between the paginators. Unfortunately, I had already studied those links and did not have any luck.

I resolved my issue by creating a custom paginator to handle the response from the Laravel server, and by disabling the paging feature. I thank you for your time nevertheless. I am now closing this issue.

Kind regards.

vc_miralpatel · February 2023

Dear Guinedd,

I am using Datatables for the past few days.I am facing the same issue that you have mentioned.
I am getting json data from API call and the data are paginated and generated by Laravel's paginate method:
API returns json in following format:

{
    "success": true,
    "data": {
        "current_page": 1,
        "data": [{
            ...,
        }],
        "first_page_url": "http:/127.0.0.1:8000/api/inventory/material/list?page=1",
        "from": 1,
        "last_page": 3,
        "last_page_url": "http:/127.0.0.1:8000/api/inventory/material/list?page=3",
        "next_page_url": "http:/127.0.0.1:8000/api/inventory/material/list?page=2",
        "path": "http:/127.0.0.1:8000/api/inventory/material/list",
        "per_page": 9,
        "prev_page_url": null,
        "to": 9,
        "total": 20
    },
    "status": 200,
    "message": "Get material list successfully",
    "links": ""
}

and table pagination code that I have done is like following:

$(document).ready(function() {
    $('#demoTable').DataTable({
        "pageLength": 10,
        "processing": true,
        "serverSide": true,
        "ajax": function(data, callback, settings) {
            console.log(data);
            console.log(callback);
            console.log(settings);
            $.get("http:/127.0.0.1:8000/api/inventory/material/list?page=1", {
                    limit: data.length,
                    offset: data.start
                },
                function(json) {
                    console.log(json);
                    console.log(json.data);
                    console.log(json.data.total);
                    callback({
                        recordsTotal: json.data.total,
                        recordsFiltered: json.data.total,
                        data: json.data
                    });
                });
        },
        "columns": [{
                "data": "id"
            },
            {
                "data": "name"
            },
        ]
    });
});

Eager to hear from you soon.
Thank you for your valuable time.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · February 2023

You are always sending page=1 there - is that intentional? Would you not need to calculate the correct value to get from the data DataTables is sending?

I wonder if you'd be better using preXhr and xhr.

For example on preXhr you might do:

$('#demoTable')
  .on('preXhr.dt', function (e, s, data) {
    s.page = data.start / data.length;
    s.offset: data.start;
    s.limit = data.length;
  })
  .on('xhr.dt', function (e, s, json) {
    // ... manipulate json for DataTables
  })
  .DataTable({
    ajax: "http:/127.0.0.1:8000/api/inventory/material/list",
    // ...
  });

While writing that I realised I don't know the difference between page and offset / limit since page can be calculated from the others? Have you got a reference for the Laravel paging API?

Allan

CamiloAcacio2023 · October 2023

Personally, this worked relatively well for me, the only problem is that the next and last buttons do not work, it is because I cannot configure the number of pages, it does bring the values according to the page number on which it is located. . I leave you my js and laravel code. Sorry if the English is not good, I use a translator because I am Colombian.

let tableUsers = $('#users').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "/Dashboard/Users/Index/Query",
                "type": "POST",
                "data": function (request) {
                    request._token = "{{ csrf_token() }}";
                    request.perPage = request.length;
                    request.page = (request.start / request.length) + 1;
                    request.search = request.search.value;
                    
                },
                "dataSrc": function (response) {
                    return response.data.users;
                }
            },
            "columns": [
                { data: 'id' },
                { data: 'name' },
                { data: 'last_name' },
                { data: 'document_number' },
                { data: 'phone_number' },
                { data: 'address' },
                { data: 'email' },
                { 
                    data: null,
                    render: function (data, type, row) {
                        return '<a href="/edit/' + data.id + '">Edit</a>';
                    }
                },
                { 
                    data: null,
                    render: function (data, type, row) {
                        return '<a href="/edit/' + data.id + '">Edit</a>';
                    }
                },
                { 
                    data: null,
                    render: function (data, type, row) {
                        return '<a href="/edit/' + data.id + '">Edit</a>';
                    }
                },
                { 
                    data: null,
                    render: function (data, type, row) {
                        return '<a href="/edit/' + data.id + '">Edit</a>';
                    }
                },
                { 
                    data: null,
                    render: function (data, type, row) {
                        return '<a href="/edit/' + data.id + '">Edit</a>';
                    }
                },
            ],
            "pagingType": "full_numbers",
            "language": {
                "paginate": {
                    "first": "Primero",
                    "last": "Último",
                    "next": "Siguiente",
                    "previous": "Anterior"
                }
            },
            "serverSide": true,
            "pageLength": 10,
            "lengthMenu": [10, 25, 50, 100],
            "paging": true,
            "info": false,
            "searching": true
        });

$start_date = Carbon::parse($request->start_date)->startOfDay();
            $end_date = Carbon::parse($request->end_date)->endOfDay();
            //Consulta por nombre
            $users = User::with('roles', 'permissions')
                ->when($request->filled('search'),
                    function ($query) use ($request) {
                        $query->search($request->search);
                    }
                )
                ->when($request->filled('start_date') && $request->filled('end_date'),
                    function ($query) use ($start_date, $end_date) {
                        $query->filterByDate($start_date, $end_date);
                    }
                )
                ->when($request->filled('role'),
                    function ($query) use ($request) {
                        $query->filterByRole($request->role);
                    }
                )
                ->paginate($request->perPage);
                
            return $this->successResponse(
                new UserIndexQueryCollection($users),
                $this->success,
                200
            );

class UserIndexQueryCollection extends ResourceCollection
{
    public function toArray($request)
    {
        return [
            'users' => $this->collection->map(function ($user) {
                return [
                    'id' => $user->id,
                    'name' => $user->name,
                    'last_name' => $user->last_name,
                    'document_number' => $user->document_number,
                    'phone_number' => $user->phone_number,
                    'address' => $user->address,
                    'email' => $user->email,
                    'created_at' => Carbon::parse($user->created_at)->format('Y-m-d H:i:s'),
                    'updated_at' => Carbon::parse($user->updated_at)->format('Y-m-d H:i:s'),
                    'roles' => $user->roles,
                    'permissions' => $user->permissions,
                ];
            }),
            'meta' => [
                'pagination' => [
                    'total' => $this->total(),
                    'count' => $this->count(),
                    'per_page' => $this->perPage(),
                    'current_page' => $this->currentPage(),
                    'total_pages' => $this->lastPage(),
                    'first_page' => $this->first_page(),
                    'last_page' => $this->last_page(),
                    'next_page' => $this->next_page(),
                    'previous_page' => $this->previous_page(),
                ],
            ],
        ];
    }

    public function first_page()
    {
        return 1;
    }

    public function last_page()
    {
        return $this->lastPage();
    }

    public function next_page()
    {
        return $this->currentPage() < $this->lastPage() ? $this->currentPage() + 1 : null;
    }

    public function previous_page()
    {
        return $this->currentPage() > 1 ? $this->currentPage() - 1 : null;
    }
}

allan · October 2023

What is the JSON returned from the server?

Allan

Zuby · October 2023

Goodday guys. I'm having major problems with this. Pls how did you solve your problem
Guineadd?

kthorngren · October 2023

@Zuby the best way to get help is to start a new thread with your Datatables config, the Javascript showing the Datatables events, ie preXhr and xhr`, you are using to manipulate the JSON data and the JSON sent in the ajax request and the JSON response using the browser's network inspector. Also provide a description if the issues you are having.

Kevin

allan · October 2023

The original post was from Feb 2022. It might be best to start a new thread with full details of the issue you are having, along with a link to a test case showing the problem. I note my last comment in this thread was for the JSON being returned - with a link to a test case, we'd be able to see that and help.

Allan

Zuby · October 2023

Ok guys. Thanks alot. I've just discovered more discussion. If I dont hey the help I need, I'll start a new thread. Thanks alot again.

How to implement a pagination model and make use of specifically formatted JSONs

How to implement a pagination model and make use of specifically formatted JSONs

Answers

Howdy, Stranger!

Categories

DataTables

How to implement a pagination model and make use of specifically formatted JSONs

How to implement a pagination model and make use of specifically formatted JSONs

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables