Update table on Ajax Request

craigooo

Hi there,

This is my first time working with Datatables (using c# mvc in the back end), and the functionality I'm trying to create is the following:

• 20 rows are sent from the server to the datatable on load.
• When you scroll to the bottom of the 20 rows, the table will load the next 20 rows. This is handled server side as the number of current table rows is posted to the server, and the next 20 are returned.

The issue I am having is this:
table.ajax.reload(null, false);
will call the server for the next 20 rows but when the rows are returned, the original 20 rows are replaced by the new 20. I want the new 20 rows to be added alongside the new 20.

One possible solution is to return 40 rows from the server when scrolling to the bottom, but I'm dealing with a large dataset, so the further you scroll, the more detrimental this is to performance. Here is the current code:

document.addEventListener('scroll', function (event) {
    if (ajaxGetWhenScrolling) {
    if (event.target.className === 'dataTables_scrollBody') { // or any other filtering condition        
        if ($(event.target).scrollTop() + $(event.target).innerHeight() >= $(event.target)[0].scrollHeight) {
            dtAdditionalData.currentRows = $('#gamesListTable >tbody >tr').length;
            var table = $("#myTable").DataTable();
            table.ajax.reload(null, false);
            }
        }
    }
}, true /*Capture event*/);

And my datatable is configured in the following way:

            dt = $('#myTable').DataTable({
        'responsive': true,
        'serverSide': true,
        'filter': true,
        'autoWidth': false,
        'scrollCollapse': true,
        'deferRender': true,
        'aaSorting': [[1, 'asc']], //initial sort by Order
        'pageLength': 20,
        'searchDelay': 500,
        'paging': true,
        'scrollY': 460,
        'scroller': true,
        'scrollX': true,
        'ajax': {
            'url': '@Url.Action("myServerSideController")',
            'type': 'POST',
            'datatype': 'json',
            "data": function (d) {
                Object.assign(d, dtAdditionalData);
                return d;
            }
                },
        'stateSave': true,
        'fixedHeader': { //fix the header on scroll past
            header: true,
            headerOffset: $('#fixed').height()
        },
        'columnDefs': [
            {
                'targets': '_all',
                'className': 'dt-center'
            },
            {
                'targets': 0,
                'checkboxes': {
                    'selectRow': true
                }
            },
            {
                //Fix and format json dates
                'targets': 11,
                'render': function (data, type, row, meta) {
                    let parsedDate = new Date(parseInt(data.substr(6)));
                    let date = moment(parsedDate)
                    let formattedDate = date.format('DD/MM/YYYY HH:mm');
                    if (date.year() < 1970) {
                        return "No end date";
                    }

                    return formattedDate
                }
            }
        ],
        'columns': [
            { "data": null, defaultContent: '' },
            { 'data': 'DisplayOrder', 'name': 'DisplayOrder', 'title': 'Order' },
            { 'data': 'Id', 'name': 'Id', 'title': 'Id' },
            { 'data': 'Name', 'name': 'Name', 'title': 'Name' },
            { 'data': 'GCD', 'name': 'GCD', 'title': 'GCD' },
            { 'data': 'GCM', 'name': 'GCM', 'title': 'GCM' },
            { 'data': 'Vend', 'name': 'Vend', 'title': 'Vend' },
            { 'data': 'SubV', 'name': 'SubV', 'title': 'SubV' },
            { 'data': 'CSD', 'name': 'CSD', 'title': 'CSD' },
            { 'data': 'Active', 'name': 'Active', 'title': 'Active' },
            { 'data': 'Published', 'name': 'Published', 'title': 'Published' },
            { 'data': 'DateCreated', 'name': 'DateCreated', 'title': 'Created' }
                ],
        'render': function (data) {
            var date = new Date(data);
            var month = date.getMonth() + 1;
            return (month.length > 1 ? month : "0" + month) + "/" + date.getDate() + "/" + date.getFullYear();
                }

    });

Any insight is much appreciated. I've been looking for a way to implement this for a few days now without using a foreach loop and using row.add (this solution also seemed to lead to poor performance)

Thanks!

sandy

Hi craigooo,

Scroller should be able to do the desired functionality for you - see this example.

You shouldn't have to use row.add(), in fact the documentation says not to. You also shouldn't need to do ajax.reload() as scroller and server side processing should do this all for you.

Are you able to link a test case for us to take a look at? Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Thanks,
Sandy

craigooo

Thanks for your quick response Sandy. Unfortunately I can't link to the project directly, but I'll start putting together an example on CodePen and send it over as soon as I can.

As for the scroller, I'm not sure I understood its purpose entirely. I have it implemented, and can see the scroller min file in my sources on chrome dev tools. But when I scroll to the bottom of the table, nothing happens.

Should I expect my serverside breakpoint (Ajax URL) to be hit each time I scroll with this?

Thanks again!

colin

Should I expect my serverside breakpoint (Ajax URL) to be hit each time I scroll with this?

It would if the buffer is exhausted. On each request, the client asks for a number of rows, it will only ask the server for more if the display area goes outside of the buffer's contents.

This example here might be helpful. It's showing how it works with serverSide,

Colin

craigooo

Hey,

I've figured it out. It came down to a massive misunderstanding of the scroller from my part.

What I was trying to do is get 20 rows of data from the server each time. This was based on my assumption that returning all of the rows from the server would be detrimental to performance. But with the scroller it really isnt.

In case anyone else comes across this as confused as I was the Scroller just loads the data into the table and what you can see is what is loaded.

Simple stuff that I've over complicated for myself but hey, thats the fun of programming!

Thanks Colin and Sandy for sending me to the correct places.