How to dynamically change columns of table in server side mode?

frandua

I have the following situation:

I have a data table that works in server side mode. The data comes via ajax requests. The data coming from the server can change the structure. The number of columns and the column names can change.

So i would need to change dynamically the "columns" definition of the datatable. In this post i read that this is not possible and therefore it is necessary to "destroy" and reinitialize the table.. at the moment it isnt working.

My question is:

How is the way to go for this kind of situation?

Below is my code.

$(document).ready(function() {
    var table;
    var columns_list = [];
    var columns_list_new = [];

   // First i do an ajax request to get the table headers.
    $.ajax("url/get/columns",   // request url
        {
            success: function (data, status, xhr) {

                // success callback function

                // data = {'keys': ['key1', 'key2', ...]}
                columns_list_new = [];
                for(i=0;i<data.keys.length;i++){
                    column_obj = {}
                    column_obj.data = data.keys[i];
                    columns_list_new.push(column_obj);
                }

                // colums_list = [{data: "key1"}, {data:"key2"}, ..]
                columns_list = columns_list_new;

                // When i have the headers, then i initialize the datatable.
                table = $('#table-main-content-id').DataTable({
                  "serverSide": true,
                  "ajax": "url/get/data",
                  "columns": columns_list,

                });

               // Listen for event. if event occurs i call for the new header (also column data) again.
                table.on('preXhr.dt', function ( e, settings, data ) {
                    $.ajax({
                        url: "url/get/columns",
                        type: "POST",
                        data: {
                            start: data.start,
                            length: data.length,
                            csrfmiddlewaretoken: '{{ csrf_token }}'
                        },
                        success: function (data, status, xhr) {// success callback function
                            columns_list_new = [];
                            for(i=0;i<data.keys.length;i++){
                                column_obj = {}
                                column_obj.data = data.keys[i];
                                columns_list_new.push(column_obj);
                            }

                           // If columns are not the same, then i reinitialize the datatable
                           // is this the way to do this?
                            if(columns_list.sort().join(',') !== columns_list_new.sort().join(',')){

                                columns_list = columns_list_new;

                                // destroy?
                                table.destroy();

                                table = $('#table-main-content-id').DataTable({
                                  "serverSide": true,
                                  "ajax": "url/get/data",
                                  "columns": columns_list,
                                });
                            }
                        }
                    });
                });
            }
    });
});

When i reinitialize the datatable i get the following error (line 58):

kthorngren

I suspect the problem is you are destroying the Datatable while it has an outstanding ajax request so its not able to complete processing.

What triggers the table structure change? Basically I'm asking if the structure changes when doing something like changing pages. Or is there another mechanism that would cause the next data fetch to change the structure.

It could be that using Datatables server side processing won't work and you will need to rely on fetching the columns and data using jQuery ajax() then using destroy() and populate the table using the data option.

Kevin

frandua

Hi Kevin,

I use server-side processing because the data comes from a database and can be very large.

In the database the data is in json format. The json can have different sizes and different keys and values (this is what I mean by different structure). Here what I want to do is that each key of the json would be a column in the datatable and the values for the keys would be displayed on the rows.

Frandua

kthorngren

Are you saying that when you go to a new page the data structure you want to display might be different? If so then I don't think using the Datatables server side processing (SSP) solution will work well. Each time you destroy and reinitialize Datatables with server side processing Datatables will start with page 1. The SSP protocol expects a consistent data structure and to not be reinitialized.

You may need to create your own paging solution to fetch the data and use data to display the data as I described above. Potentially you can use one jQuery ajax() request sending page number, page size and search term. The server script can then fetch the data, similar to SSP, and respond with the data for the page. In the success function parse the JSON response to extract the columns and data. Destroy and reinitialize Datatables using the column definition and data to display the data.

Kevin

frandua

Okay. Thank you very much for your suggestion. I'll try to do it as you mentioned. Is there an example of a custom paging implementention somewhere? How should this be done? I think i will need to make a custom ajax request for pagination...

Frandua

kthorngren

There aren't any examples of custom paging on this site. Likely you will want to disable the pageLength, search and paging options. Maybe event the info option. The create your own inputs for these and pass the via the jQuery ajax() data option to the server. Take a look at the SSP protocol docs to get an idea of what is sent using SSP.

When there is a change to any of these inputs you will want to fetch the new data via jQuery ajax() with the data parameter set to the values of the inputs. The server script will process the parameters and fetch the page data. The success function will build the columns, like you have now, destroy the Datatable and reinitialize using the data to load the table.

Kevin