how to get the dynamic column header and result from ajax call in jquery datatable

Question

I want to display the dynamic column header along with the results in datatable.In aaData and aoColumns attributes has to get result data and columns name from ajax call, Please suggest me how to do this or give me some alternate solution to get the dynamic data and column header from ajax call, Here is my code.:

var $table=$('#MSRRes').dataTable( {
    "bFilter": false,                         
    "bDestroy": true,
    "bDeferRender": false,
    "bJQueryUI": true,
    "oTableTools": {
        "sSwfPath": "swf/copy_cvs_xls_pdf.swf",
    },
    "sDom": 'TC<"clear">l<"toolbar">frtip',
    "ajax" :{
        url: 'getResult.php',
        type: "POST",
        data: {
           formData:postData,
         }  
    },
    "aaData": results.DATA ,
    "aoColumns": [ column_names ]
});

Here is my ajax call to get the result data and column names to be display:

$result=$afscpMsrMod->getAdvanceSearchResults($colCond,$whereCond,$having);
foreach($cols as $col) {
    array_push($colArr, $colnames);
}
$colJson= json_encode($colArr);
$newarray = array(
    "draw"            => 1,
    "recordsTotal"    => sizeof($result),
    "recordsFiltered" => sizeof($result),
    "data"            => $result,
    "COLUMNS"         => $colJson   
);
echo json_encode($newarray);

Solution 1

There doesn't seem to be a way to have dynamic column names using functionality within DataTables. You can work around this, if you do the ajax request yourself, (e.g. $.ajax) and then on the .complete of the ajax request, set the DataTable columns options appropriately using the ajax data you just got back, and then create the datatable. This also means that you can never simply reload your table data, but you will need to re-initialise the table each time data is requested.

Steps:

1. Manually do an ajax request yourself
2. Use that ajax data to construct the column object that you will pass to the DataTables columns option.
3. Initialise your datatable using the column object you created in step 2, and using the data returned in step 1.

Note: The DataTable itself will not need to make any ajax requests, since you already have the data from step 1.

EDIT: Here's an example using JQuery to make the ajax request:

// Assume this object is the json that the ajax request returns.
{
    customcols: ['lah', 'dee', 'dah'],
    mydata: [
        {
            lah: "value1",
            dee: "value2",
            dah: "value3",
        },
        {
            lah: "value4",
            dee: "value5",
            dah: "value6",
        },
        ]
}

Then, in response to something, something_happened gets called.

function something_happened(){
    $.ajax('/whatever/your/ajax/address/is')
        .done(maketable)
}

function maketable(data){
    var data = data.mydata;
    var column_names = data.customcols;
    var columns = []
    for (var i = 0; i < column_names.length; i++) {
        columns[i] = {
            'title': column_names[i],
            'data': column_names[i]
        }
    };
    $('#someplaceholder').DataTable({
        columns: columns,
        data: data,
    })

}

This example makes use of "Using an array of objects as a data source" (see http://datatables.net/reference/option/data).

Solution 2

There is my example of it with ASP.NET MVC

Repo Method

public List<Koef> GetColumnHeaders(int id)
{
    using (var _db = new DB_RiskiEntities())
        return _db.Koefs.Where(x => x.id_group == id).ToList();
}

Controller

[HttpPost]
public ActionResult GetColumnHeaders(int? id)
{
    return Json(
        _RiskiRepo.GetColumnHeaders(id ?? 0).Select(x => new { title = x.name })
        );
}

JS

var Init = function () {
        $.ajax({
            url: "/Home/GetColumnHeaders",
            type: "POST",
            data: { id: group_id },
            success: function (result) {
                table = $('#tableid').DataTable({
                    "ajax": "/Home/GetMainDataAjax",
                    "columns": result,
                });

            }
        });
};

Using

var FocusGroupChanged = function (_groupId) {
    group_id = _groupId;
    table.destroy();
    $('#tableid').empty();
    Init();
};

Solution 3

I suggest the following approach. Ensure that the incoming JSON from AJAX request looks like

{
    title: "Super Awesome AJAX DataTable",
    column_titles: [...],
    data: [[...], [...], [...], ...],
    ...
}

Now in the HTML/JS template use this snippet.

/* 
  params: JS Object containing GET parameters
  Optionally pass JSON URL source. 
*/
function updateTable(params) {
    $.getJSON(
        window.location.pathname,
        params,
        function(table) {
            // Set table title
            $('#title_box').text(table.title);

            // Set table headers
            var column_titles = table.column_titles.map(function(header) {
                return {
                    'title': header
                };
            });

            // Let datatables render the rest.
            $('#datatable').dataTable({
                "ordering": false,
                "searching": false,
                "paging": false,
                "info": false,
                "columns": column_titles,
                "data": table.data
            });
        }
    );
}

This technique can be easily extended to set table titles, footers, CSS classes etc.

Comments

[user3829086]

I saw the option to pass the dynamic columns along with the result data, but I am not getting how to process after the ajax call.

[ZenCodr]

ALso I recommend upgrading to the newest version of DataTables and having a look at datatables.net/upgrade/1.10-convert for variable name changes and removed options

[MoFarid]

Awesome, can you tell me what the view would be like? thanks

[slava]

@MuhFred In this project I had just this <table id="tableid" > <thead></thead> <tbody></tbody> </table> in a view. Add +1 if it helped you.

[TAHA SULTAN TEMURI]

its a waste of time , first you run query to fetch headers only then again run the same query to get rows ? why