Server-side Pagination in Sails with DataTables

Question

I am currently using the server-side option of DataTables, my current problem is that the page numbers in the data table only displays 1. To further explain here is my code:

Javascript:

var oTable1 = $('#users-table').dataTable({
    "bDestroy": true,
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/v1/users",
    "bPaginate": true,
    "aoColumns": [
        { "mData": "id" },
        { "mData": "username" },
        { "mData": "name" },
        { "mData": "acctype" },
        { "mData": "country" },
        { "mData": "createdAt" },
        { "mData": "button" }
    ]
});

/v1/users:

viewAll: function(req, res, next) {

    var colS = "";

    switch(req.query.iSortCol_0){
        case '0': colS = 'id'; break;
        case '1': colS = 'username'; break;
        case '2': colS = 'firstname'; break;
        case '3': colS = 'acctype'; break;
        case '4': colS = 'country'; break;
        case '5': colS = 'createdAt'; break;
        default: break;
    };

    var options = {
        sort: colS+' '+req.query.sSortDir_0,
        or: [
            { acctype: { 'contains': req.query.sSearch }},
            { id: { 'contains': req.query.sSearch }},
            { username: { 'contains': req.query.sSearch }},
            { lastname: { 'contains': req.query.sSearch }},
            { firstname: { 'contains': req.query.sSearch }},
            { country: { 'contains': req.query.sSearch }},
            { createdAt: { 'contains': req.query.sSearch }}
        ],
        limit: req.query.iDisplayLength
    };

    console.log(req.query);
    console.log(req.query.sSearch);
    //console.log(options);
    var p = req.query.iDisplayStart+1;

    //console.log(req.query.iDisplayStart);
    Users.find(options).paginate({ page: p, limit: req.query.iDisplayLength }).exec(function(err, usr){
        if(err){
            res.send(500, { error: 'DB error' });
        } else {
            var retuser = [];
            usr.forEach(function(user){
                retuser.push({ id: user.id, username: user.username, name: user.firstname+" "+user.lastname, acctype: user.acctype, country: user.country, createdAt: user.createdAt, button: "<a class='blue' href='#view-user' data-toggle='modal' id='user-info'> <i class='icon-zoom-in bigger-130'></i></a>" });
            });

            Users.count().exec(function(err, result){
                var json = {
                    aaData: retuser,
                    iTotalRecords: result,
                    iTotalDisplayRecords: usr.length
                };
                res.contentType('application/json');
                res.json(json);
            });
        }
    });
},

What basically happens is that: When I change the page in

.paginate({ page: p, limit: req.query.iDisplayLength })

to 2, it shows what should be displayed in page 2, and if I change it to 1, it shows what should be displayed in page 1. And when change the number of records to display, it shows the 11th record. So I guess there is no problem with regards to how I retrieve my data from the controller.

I thought that DataTables automatically adds page numbers based on the iTotalDisplayRecords and iTotalRecords, but it turns out that it isn't. Is there something wrong with my code or what I thought was wrong, that I'd have to be the one to add the page numbers in my DataTable?

Answer 1

I believe the problem is that you're returning wrong value in iTotalDisplayRecords:

"iTotalRecords": 11, 
"iTotalDisplayRecords": 10

From the manual:

int iTotalRecords
Total records, before filtering (i.e. the total number of records in the database)

int iTotalDisplayRecords
Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set)

When filtering is not performed, iTotalRecords should be equal to iTotalDisplayRecords.

To correct the problem, you need to calculate total number of records after filtering has been applied and use that value for iTotalDisplayRecords instead of usr.length.