Paging & Column Visibility In Server Side

NoBullMan

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I have to display around 54,000 records and it seems server side processing is the way to do it. Unfortunately, I have never done this, so this is my first stab at jQuery Datatables and server side processing and running into some issues.
I can get the data and display it but it only displays 25 record (default page size I set) and does not provide paging.

When I check the data being sent to data table I can see total records is at 53K+ and page size is 25, "data" contains array of 25 records but paging not showing up.

Another issue I am facing is that I set viability for columns 8, 9 and 10 to false but they are displayed anyways. I set it in columndef, as part of drwaCallback, as well as using column.visible(); none worked.

I googled the heck of these issue but nothing helped. I am hoping you can spot what I am doing wrong or missing.

function bindSitesTable() {
    tblSites = $("#sitesTable").DataTable({
        serverSide: true,
        ajax: {
            url: '<%= ResolveUrl("../WebService/IDC.asmx/GetSitesData") %>',
            contentType: "application/json",
            type: "GET",
            dataType: "JSON",
            data: function (d) {
                return d;
            },
            //data: function (data) {  // I will have to use start and end date and time, once current issues are resolved
            //    // custom parameters to the data object
            //    data.StartDate = startDate;
            //    data.StartTime = startTime;
            //    data.EndDate = endDate;
            //    data.EndTime = endTime;
            //    return data;
            //},
            dataSrc: function (json) {
                json.draw = json.d.draw;
                json.recordsTotal = json.d.recordsTotal;
                json.recordsFiltered = json.d.recordsFiltered;
                json.data = json.d.data;
                var return_data = json;
                return return_data.data;
            }
        },
        jQueryUI: true,
        dom: '<l<t>ip>',
        order: [[2, 'asc'],[7, 'desc'],],
        autoWidth: true,
        paging: true,
        lengthMenu: [[25, 50, 100, -1], [25, 50, 100, "All"]],
        columns: [
            {
                data: "FDBID"
            }, {
                data: "OriginZip"
            }, {
                data: "SiteName"
            }, {
                data: "Address"
            }, {
                data: "CityState"
            }, {
                data: "ContactName"
            }, {
                data: "ContactPhone"
            }, {
                data: "LastUpdate",
                render: function (data, type, row) {
                    if (data == null) {
                        return '';
                    }
                    else {
                        if (data != '') {
                            return type === 'sort' ? data : moment(data).format('MM/DD/YYYY');
                        }
                    }
                    return data;
                }
            }, {
                data: "OriginID"
            }, {
                data: "ZIP"
            }, {
                data: "PlusFour"
            }
        ],
        columnDefs: [
            {
                targets: [8, 9, 10],
                visible: "false"
            }
        ],
        //drawCallback: function () {
        //    $('td:nth-child(8),th:nth-child(8)').hide();
        //    $('td:nth-child(9),th:nth-child(9)').hide();
        //    $('td:nth-child(10),th:nth-child(10)').hide();
        //},
        pageLength: 25,
        processing: true,
        deferRender: true,
    }).column([8,9,10]).visible(false);
}

in C# code:

public class SiteDataTable
{
    public int draw {get; set;}
    public int recordsTotal {get; set;}
    public int recordsFiltered {get; set;}
    public List<SiteInfo> data {get; set;}
}

[WebMethod]
[ScriptMethod(UseHttpGet = true,ResponseFormat = ResponseFormat.Json)]
public object GetAtlasSitesData(int draw, int start, int length)
{
    int sortColumn = -1;
    string sortDirection = "asc";

    // only sort one column at a time
    if (HttpContext.Current.Request.QueryString["order[0][column]"] != null)
    {
        sortColumn = int.Parse(HttpContext.Current.Request.QueryString["order[0][column]"]);
    }
    if (HttpContext.Current.Request.QueryString["order[0][dir]"] != null)
    {
        sortDirection = HttpContext.Current.Request.QueryString["order[0][dir]"];
    }
    SiteDataTables result = new SiteDataTables();

    // Capture Jquery Datatables Plugin Properties.
    string search = HttpContext.Current.Request.Params["search[value]"];
    //string draw = HttpContext.Current.Request.Params["draw"];
    string order = HttpContext.Current.Request.Params["order[0][column]"];
    string orderDir = HttpContext.Current.Request.Params["order[0][dir]"];
    int startRec = Convert.ToInt32(HttpContext.Current.Request.Params["start"]);
    int pageSize = Convert.ToInt32(HttpContext.Current.Request.Params["length"]);

    int recFilter = 0;
    int totalRecords = 0;

    // Load data.
    List<SiteInfo> data = FilterData(ref totalRecords, ref recFilter, startRec, pageSize, search, sortColumn, orderDir);

    // Configure Jquery Datatable property Total record count property.
    //int totalRecords = data.Count;

    // Configure Jquery Datatable property Filter record count property after applying searching and sorting.
    recFilter = data.Count;

    // Apply server-side pagination.
    // ???

    // Mapping final configuration settings for Jquery Datatables plugin.
// When I put a breakpoint here, I see it get hit 3 times, all below values remain the same except draw changes from 1 to 2 to 3

    result.draw = Convert.ToInt32(draw);   // 1
    result.recordsTotal = totalRecords;       // 53926
    result.recordsFiltered = recFilter;          // 25
    result.data = data;                                 // Shows as "Count=25"

    // Return info.
    return result;
}

private static List<SiteInfo> FilterData(ref int recordTotal, ref int recordFiltered, int start, int length, string search, int sortColumn, string sortDirection)
{
    List<SiteInfo> _data = ABC.GetSitesDataList();
    List<SiteInfo> list = new List<SiteInfo>();

    if (string.IsNullOrEmpty(search))
    {
        list = _data;
    }
    else
    { 
        // Apply server-side data searching
        foreach (SiteInfo dataItem in list)
        {
            if (dataItem.SiteName.ToUpper().Contains(search.ToUpper()) ||
                dataItem.Address.ToString().Contains(search.ToUpper()) ||
                dataItem.CityState.ToString().Contains(search.ToUpper()) ||
                dataItem.ContactPhone.ToString().Contains(search.ToUpper()) ||
                dataItem.FDBID.ToString().Contains(search.ToUpper()) ||
                dataItem.LastUpdate.ToString().Contains(search.ToUpper()) ||
                dataItem.OriginZip.ToString().Contains(search.ToUpper()) ||
                dataItem.SiteName.ToString().Contains(search.ToUpper()))
            {
                list.Add(dataItem);
            }
        }
    }
    //// Apply server-side Sorting -- Is this the way to do it; seems cumbersome!
    if (sortColumn == 0)
    {// sort Name
        list.Sort((x, y) => SortString(x.FDBID, y.FDBID, sortDirection));
    }
    if (sortColumn == 1)
    {// sort Name
        list.Sort((x, y) => SortString(x.OriginZip, y.OriginZip, sortDirection));
    }
    if (sortColumn == 2)
    {// sort Name
        list.Sort((x, y) => SortString(x.SiteName, y.SiteName, sortDirection));
    }
    if (sortColumn == 3)
    {// sort Name
        list.Sort((x, y) => SortString(x.Address, y.Address, sortDirection));
    }
    if (sortColumn == 4)
    {// sort Name
        list.Sort((x, y) => SortString(x.CityState, y.CityState, sortDirection));
    }
    if (sortColumn == 5)
    {// sort Name
        list.Sort((x, y) => SortString(x.ContactName, y.ContactName, sortDirection));
    }
    else if (sortColumn == 6)
    {// sort Age
        list.Sort((x, y) => SortInteger(x.ContactPhone, y.ContactPhone, sortDirection));
    }
    else if (sortColumn == 7)
    {   // sort DoB
        list.Sort((x, y) => SortDateTime(x.LastUpdate, y.LastUpdate, sortDirection));
    }
    recordFiltered = list.Count;
    recordTotal = _data.Count;

    // get just one page of data
    list = list.GetRange(start, Math.Min(length, list.Count - start));

    return list;
}

kthorngren

Another issue I am facing is that I set viability for columns 8, 9 and 10 to false but they are displayed anyways.

You have false inside quotes but it's a boolean value:

        columnDefs: [
            {
                targets: [8, 9, 10],
                visible: "false"
            }
        ],

It should be visible: false.

Not sure why chaining }).column([8,9,10]).visible(false); doesn't work as it works in this test case:
https://live.datatables.net/hunotoxa/1/edit

And your drawCallack code seems to work in this test case:
https://live.datatables.net/baqobaja/1/edit

I would stick with columns.visible for future code clarity.

does not provide paging.

Is suspect the value for recordsFiltered is incorrect, for example 25. Returning the page length value will result in in only one paging button - is this what you mean by "does not provide paging"? This is from the Server Side Processing Protocol docs for the recordsFiltered parameter:

Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned for this page of data).

Use the browser's network inspector to see what is returned for recordsFiltered.

If you still need help then please post a test case replicating the issues so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

NoBullMan

Thank you Kevin. That boolean in quotes was such an oversight; didn't see it.

Also, you are correct; recordsFiltered is 25 but for some reason the function that returns filtered and total counts gets hit twice; the first time total and filtered are same (53k) which is how it should be since initially there is no search filtering. But the second time it hits the same function, filtered count changes to 25. I guess that's where the problem is: why does it hit this function twice. Not sure if anything in table initialization causes the Ajax method to be called twice.

kthorngren

why does it hit this function twice. Not sure if anything in table initialization causes the Ajax method to be called twice.

Are you seeing two XHR requests in the browser's network inspector?

I don't see anything in your init code snippet that would cause a second Ajax request. Calling something like draw() or ajax.reload() will send another request to the server. Do you have something calling one of these API's? If yes have you confirmed its not called on page load?

Kevin

NoBullMan

Yes, I do see two xhr, same size, 1 second apart, says initiated by jquery.min.js.

I am not specifically calling draw, aside for in initComplete that handles column search, when user types in in text box in column header. But this is happening in initial load of the page.

NoBullMan

I have the datatable initialized inside a function called bindSiteTable that is called in document.ready(). This should not cause any issues, correct?

allan

This should not cause any issues, correct?

I don't believe so from your description. If you can link to a test case showing the issue I'd be happy to take a look at what is going on.

Allan

kthorngren

Is the function bindSiteTable() called twice during the page load? Use the browser's debugger and place a breakpoint on line 2 of the function to check for this.

Kevin

NoBullMan

I will check and if can't still figure it out, I will try to make a test case.

On a different note: on the server side, to check for search string, I use:

string search = HttpContext.Current.Request.Params["search[value]"] 

but this seems to return whatever is in the search text box. How do I access column filtering text searches, text boxes for individual/specific column?

kthorngren

I'm not familair with C# so not sure of the specifics but basically you will need to loop through all the columns[i] parameters as shown in the Server Side Processing protocol docs. You can see a sample of this in the ssp.class.php filter function. Line 196 starts the individual column search code.

Kevin

NoBullMan

I am not familiar with PHP but looking at the code, the filter section, it is looping through $request["columns"]. Its equivalent in C# would be Request.Params["columns"]. I get null when I put a break-point there.

Is this the proper way of getting array/list of table columns?

kthorngren

I'm not sure what your environment. The Editor supports a few different environments. See the download page. The server side. libraries are open source allowing use of them without the license. See this blog for more details.

If you are unable to use one of the Editor supplied libraries then maybe @allan will have suggestions for accessing the request parameters.

Kevin

NoBullMan

Thank you Kevin. I am not using Editor. I am just trying to see how I can access the data table's columns collection on server side, for instance through "http request" object that allows me to check column order, order direction, etc., so I can iterate through column collection and see which column has search string (column filter processing).

I thought there might be an easier way of doing this:

string a = Request.Params["columns[0][search][value]"];
string b = Request.Params["columns[1][search][value]"];
...;
string z = Request.Params["columns[10][search][value]"];

NoBullMan

I go the page to display all data and paging to work.
Now, when I try to process column filter and check "columns[i][search][value]" I see "((((b))))" even though nothing is entered in column filter.

kthorngren

Use the browser's network inspector to see what is sent for that parameter. Let us know what you find.

Kevin

NoBullMan

Kevin, do you know what part of data table's js file deals with column search/filter?
I want to see if I can change it to search as "startsWith" instead of "contains" when user types a character in column filtering textbox.
Or, if I have an option in initCompete to force it to use "starts with".
This is for client side and not server side.

NoBullMan

never mind. I changed:

api
    .column(colIdx)
    .search(
        this.value != ''
            ? regexr.replace('{search}', '(((' + this.value + ')))')
            : '',
        this.value != '',
        this.value = ''
    )
    .draw();

with:

api
    .column(colIdx)
    .search(
        this.value != ''
            ? regexr.replace('{search}', '(((^' + this.value + ')))')
            : '',
        this.value != '',
        this.value = ''
    )
    .draw();

(I added a '^' after '(((' )

kthorngren

this.value = ''

This si an assignment not a comparison. I think you want this.value === '' for a boolean value.

after '((('

I don't think you need the regex expression inside the parenthesis. PRobably all you need is '^' + this.value.

Glad you got it working though.

Kevin