How to prepend data to a dataTable ?

CyrilF

Hi,

I'm new with dataTable and I'd like to improve a code I got that uses this feature to sort columns. I want to add new rows at the front via AJAX.

I found that the fnAddData() function could do the trick but this is not clear how to use it in the documentation.

Here is my HTML code:

<table id="uses" class="table table-condensed table-striped table-bordered table-hover table-highlight table-checkable" 
        data-provide="datatable" 
        data-info="true"
        data-search="true">
 <thead>
    <tr>
        <th data-mdata="euroDate" data-sortable="true">Date</th>
        <th data-mdata="ignoreColumnAccents" data-sortable="true">Immeuble</th>
        <th data-mdata="ignoreColumnAccents" data-sortable="true">Utilisateur</th>
        <th data-mdata="ignoreColumnAccents" class="hidden-xs hidden-sm">Terminal</th>
        <th data-mdata="ignoreColumnAccents">Machine</th>
        <th>Durée</th>
        <th>Montant</th>
    </tr>
 </thead>
 <tbody>
    ....
 </tbody>
</table>

And my JS:

$(function() {
    $("#uses").dataTable().fnAddData([
        "08.02.2016 15:05:18",
        "17th O'Connore Street",
        "John Doe",
        "TH33",
        "MM26",
        "15'",
        "21.0"
    ], false);
});

I get this error and no clear explanation on why:

DataTables warning (table id = 'uses'): Requested unknown parameter {mData function} from the data source for row 1

jr42.gordon

The current way to add a new row is by using row.add()

https://datatables.net/examples/api/add_row.html

allan

You've confirmed your table to expect objects, so you need to use objects in your fnAddData / row.add() call.

Not also that the order of the data in the table is entirely defined by the sorting that is applied to the table. If you want the new row to appear at the top of the table, you need to make sure that the data in it will cause it to be sorted to the top.

Allan

CyrilF

Sorry I'm a bit busy these days.

With

$("#uses").dataTable().api().row.add([
        "08.02.2016 15:05:18",
        "17th O'Connore Street",
        "John Doe",
        "TH33",
        "MM26",
        "15'",
        "21.0"
]);

I get

DataTables warning: table id=uses - Requested unknown parameter {function} for row 7, column 1. For more information about this error, please see http://datatables.net/tn/4

My helpers:

function ignoreColumnAccents(idx,data,type,val) {
    if (!data.noaccent) data.noaccent=[];
    if (!data.noaccent[idx]) data.noaccent[idx]={};
    if (type === "set") {
       data.noaccent[idx].display_value = val;
       data.noaccent[idx].fliter_value = removeAccents(val);
       return;
    } else if (type === "filter" || type === "sort") {
        return data.noaccent[idx].fliter_value;
    }
    return data.noaccent[idx].display_value;
}

function euroDate(idx,data,type,val) {
    if (!data.date) data.date=[];
    if (!data.date[idx]) data.date[idx]={};
    if (type === "set") {
        data.date[idx].display_value = val;
        if ($.trim(val) != '') {
            var frDatea = $.trim(val).split(' ');
            var frDatea2 = frDatea[0].split('.');
            var x = (frDatea2[2] + frDatea2[1] + frDatea2[0] + "00" + "00" + "00") * 1;
        } else {
            var x = 10000000000000; // = l'an 1000 ...
        }
        data.date[idx].sort_value=x;
        return;
    } else if (type === "sort") {
        return data.date[idx].sort_value;
    }
    return data.date[idx].display_value;
}

allan

That error suggests your function is returning undefined or null for that row / column.

CyrilF

What function ?

allan

Whatever function you have assigned to that column for data - the error message notes that it is a function. Without access to the page I can't really say more I'm afraid.

Allan

jr42.gordon

Can you paste your DataTable configuration?

CyrilF

The first column uses euroDate() function and the 4 nexts ignoreColumnAccents(). But I cannot figure out how to pass those functions to row.add(). It should simply add the values directly.

Here is the function that init the dataTable:

function initDataTableHelper() {
    if ($.fn.dataTable) {
        $('[data-provide="datatable"]').each (function () { 
            $(this).addClass ('dataTable-helper');      
            var defaultOptions = {
                    paginate: false,
                    search: false,
                    info: false,
                    lengthChange: false,
                    displayRows: 10
                },
                dataOptions = $(this).data (),
                helperOptions = $.extend (defaultOptions, dataOptions),
                $thisTable,
                tableConfig = {};
            tableConfig.iDisplayLength = helperOptions.displayRows;
            tableConfig.bFilter = true;
            tableConfig.bSort = true;
            tableConfig.bPaginate = false;
            tableConfig.bLengthChange = false;  
            tableConfig.bInfo = false;
            if (helperOptions.processing) { tableConfig.bProcessing = true; }
            if (helperOptions.serverside) { tableConfig.bServerSide = true; }
            if (helperOptions.ajaxsource) { tableConfig.sAjaxSource = helperOptions.ajaxsource; }
            if (helperOptions.serverparams) {
                var p=helperOptions.serverparams;
                var params="";
                for (var i=0;i<p.length;i++) {
                    params+='aoData.push( { "name": "'+p[i].name+'", "value": "'+p[i].value+'" } );';
                }
                eval("tableConfig.fnServerParams=function ( aoData ) { "+params+"}");
            }
            if (helperOptions.paginate) { tableConfig.bPaginate = true; }
            if (helperOptions.lengthChange) { tableConfig.bLengthChange = true; }
            if (helperOptions.info) { tableConfig.bInfo = true; }       
            if (helperOptions.search) { $(this).parent ().removeClass ('datatable-hidesearch'); }               

            tableConfig.aaSorting = [];
            tableConfig.aoSearchCols = [];
            tableConfig.aoColumns = [];

            $(this).find ('thead tr th').each (function (index, value) {
                var sortable = ($(this).data ('sortable') === true) ? true : false;
                var mdata = $(this).data ('mdata');
                var co={ 'bSortable': sortable};
                var coso = $(this).data ('search');
                if (coso) {
                    tableConfig.aoSearchCols.push ({"sSearch":coso});
                } else {
                    tableConfig.aoSearchCols.push (null);
                }
                if (mdata) {
                    if (helperOptions.serverside) {
                        co.mData=mdata;
                    } else {
                        var tf="function(data,type,val) { return "+mdata+"("+index+",data,type,val);}";
                        eval("co.mData="+tf);
                        }
                }
                tableConfig.aoColumns.push (co);

                if ($(this).data ('direction')) {
                    tableConfig.aaSorting.push ([index, $(this).data ('direction')]);
                }
            });     

            // Create the datatable
            $thisTable = $(this).on('init.dt', function() {
                initDataTableResponsiveScroll();
            }).dataTable(tableConfig);

            if (!helperOptions.search) {
                $thisTable.parent ().find('.dataTables_filter').remove ();
            }

            var filterableCols = $thisTable.find ('thead th').filter('[data-filterable="true"],[data-selectable="true"]');

            if (filterableCols.length > 0) {
                var columns = $thisTable.fnSettings().aoColumns,
                    $row, th, $col, showFilter;

                $row = $('<tr>', { cls: 'dataTable-filter-row' }).appendTo ($thisTable.find ('thead'));

                for (var i = 0; i < columns.length; i++) {
                    $col = $(columns[i].nTh.outerHTML);
                    showFilter = ($col.data ('filterable') === true || $col.data ('selectable') === true) ? 'show' : 'hide';

                    th = '<th class="' + $col.prop ('class') + '">';
                    if ($col.data ('selectable') === true) {
                        th += '<select class="form-control input-sm ' + showFilter + '" placeholder="' + $col.text () + '">';
                        th += '<option></option>';
                        var so=$col.data("select-options");
                        var soval=$col.data("search");
                        for (var i2=0;i2<so.length;i2++) {
                            var sovalue=(so[i2].value?so[i2].value:so[i2]);
                            var soname=(so[i2].name?so[i2].name:so[i2]);
                            th+='<option value="'+sovalue+'"';
                            if (soval && soval==sovalue) th+=' selected="true"';
                            th+='>'+soname+'</option>';
                        }
                    } else {
                        var dataSrc=$col.data('typeahead-src');
                        th += '<input type="text" ' + (dataSrc?'data-src="'+dataSrc+'"':"") +' class="form-control input-sm ' + showFilter + (dataSrc?" typeahead":"")+ '" placeholder="' + $col.text () + '">';
                    }
                    th += '</th>';
                    $row.append (th);
                }

                $row.find('th').removeClass ('sorting sorting_disabled sorting_asc sorting_desc sorting_asc_disabled sorting_desc_disabled');

                $thisTable.find('thead select').change( function() {
                    $thisTable.fnFilter( this.value, $thisTable.oApi._fnVisibleToColumnIndex( 
                        $thisTable.fnSettings(), $thisTable.find ('thead :input').index(this) ) );
                });
                $thisTable.find('thead input').not(".typeahead").keyup( function () {
                    $thisTable.fnFilter( this.value, $thisTable.oApi._fnVisibleToColumnIndex( 
                        $thisTable.fnSettings(), $thisTable.find ('thead :input').index(this) ) );
                });
                var dtO=$.extend({}, dtTypeAheaderHandler);
                dtO.dataTable = $thisTable;
                dtO.dataTableIdx = i;
                $thisTable.find('thead input.typeahead').typeahead(dtO);
                $thisTable.addClass('datatable-columnfilter');
            }
        });
        $('.dataTables_filter input').prop('placeholder', 'Search...');
    }
}

jr42.gordon

Utilize following in your columnDef

https://datatables.net/examples/advanced_init/column_render.html

CyrilF

How should I pass columnDef to row.add()?

jr42.gordon

You don't. The render function should be attached your 'co' variable which is then attached to 'tableConfig.aoColumns.push (co);'

When you add a new row and have assigned a render function to a column, DataTables will use it. Otherwise, it will simply output whatever data was passed to it within your array. Also, within the render function you will call either ignoreColumnAccents() or euroDate();

jr42.gordon

http://live.datatables.net/miyucida/4/edit?html,js,output

I came up with a quick example to illustrate how I think you could make your DataTable init a lot simpler. As far as your original question about prepending new data, that is where the hidden "Sort Order" column comes into play. Click on the [Add Data] button to see the new data get added and sorted to show up at the top.

Obviously it would be much more complex in your case, but ultimately you want to make any new data have a lower/higher value, depending on your sort direction, than what currently exists for that column in the table.

CyrilF

OK this is more clear for me about this.

But it seems that I have a completely different API. Here is the configuration passed to dataTable() in the initDataTableHelper() function and shown in FireBug for the table tag in my first post:

{
    aaSorting: [],
    aoColumns: [{
        bSortable: true,
        mData: function(data, type, val)
    },
    {
        bSortable: true,
        mData: function(data, type, val)
    },
    {
        bSortable: true,
        mData: function(data, type, val)
    },
    {
        bSortable: false,
        mData: function(data, type, val)
    },
    {
        bSortable: false,
        mData: function(data, type, val)
    },
    {
        bSortable: false,
        mData: function(data, type, val)
    },
    {
        bSortable: false,
        mData: function(data, type, val)
    }],
    aoSearchCols: [{
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    },
    {
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    },
    {
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    },
    {
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    },
    {
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    },
    {
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    },
    {
         bCaseInsensitive: true,
         bRegex: false,
         bSmart: true,
         sSearch: ""
    }],
    bFilter: true,
    bInfo: true,
    bLengthChange: false,
    bPaginate: true,
    bSort: false,
    iDisplayLength: 10,
    info: true,
    oSearch: Object{},
    provide: "datatable",
    search: true
}

As you can see, there is no "columnDef", no "render", etc.

jr42.gordon

aoColumns and aoColumnDefs performs the same functionality, so you are good. Your two functions "euroData" and "ignoreColumnAccents" should be called within your mData function for the appropriate column.

Now referring to your error

DataTables warning: table id=uses - Requested unknown parameter {function} for row 7, column 1. For more information about this error, please see http://datatables.net/tn/4

There was an issue with the data for the second column.

CyrilF

I tried to pass a function like in the init:

var table = $("#uses").dataTable().api();
table.row.add([
    { "mData": function(data, type, val) { return "08.02.2016 15:05:18"; }  },
    { "mData": function(data, type, val) { return "17th O'Connore Street"; }  },
    { "mData": function(data, type, val) { return "John Doe"; }  },
    { "mData": function(data, type, val) { return "TH33"; }  },
    { "mData": function(data, type, val) { return "MM26"; }  },
    "15'",
    "21.0"
 ]);

The "15'" and "21.0" cells are correctly added but I still have an issue with the other columns.

DataTables warning: table id=uses - Requested unknown parameter {function} for row 25, column 0. For more information about this error, please see http://datatables.net/tn/4

I also tried to pass directly the function euroDate() or ignoreColumnAccents() like

{ "mData": function(data, type, val) { return euroDate(1, data, type, val); }  },

With the same error :/

jr42.gordon

You should not be adding a new row like that. The original way you were adding it is correct.

.dataTable().api().row.add([
        "08.02.2016 15:05:18",
        "17th O'Connore Street",
        "John Doe",
        "TH33",
        "MM26",
        "15'",
        "21.0"
]);

The way you configured your table and columns applies to current and future rows.

The mData functions attached to your table init should look something like this

aoColumns: [{
        bSortable: true,
        mData: function(data, type, val) {
               euroDate(idx,data,type,val);
        }
    },
    {
        bSortable: true,
        mData: function(data, type, val) {
               ignoreColumnAccents(idx,data,type,val);
        }
    },
    {
        bSortable: true,
        mData: function(data, type, val) {
               ignoreColumnAccents(idx,data,type,val);
        }
    },
    {
        bSortable: false,
        mData: function(data, type, val) {
               ignoreColumnAccents(idx,data,type,val);
        }
    },
    {
        bSortable: false,
        mData: function(data, type, val) {
               ignoreColumnAccents(idx,data,type,val);
        }
    },
    {
        bSortable: false,
        mData: function(data, type, val) {
               // not sure you need this since column appears to just be output
        }
    },
    {
        bSortable: false,
        mData: function(data, type, val) {
               // not sure you need this since column appears to just be output
        }
    }],

If you still have issues, read this link https://legacy.datatables.net/ref#mData and consider using mRender.

allan

I don't understand why you are trying to pass mData into the row.add() function!

Just pass the data object you want to use:

table.row.add( [
  "08.02.2016 15:05:18",
  "17th O'Connore Street",
  ...
] ).draw();

Allan

CyrilF

@jr42.gordon : OK.

Yes this is how the configuration was done by initDataTableHelper() with:

$(this).find ('thead tr th').each (function (index, value) {
     var sortable = ($(this).data ('sortable') === true) ? true : false;
     var mdata = $(this).data ('mdata');
     var co={ 'bSortable': sortable};
     var coso = $(this).data ('search');
     if (coso) {
         tableConfig.aoSearchCols.push ({"sSearch":coso});
     } else {
         tableConfig.aoSearchCols.push (null);
     }
     if (mdata) {
         if (helperOptions.serverside) {
             co.mData=mdata;
         } else {
             var tf="function(data,type,val) { return "+mdata+"("+index+",data,type,val);}";
             eval("co.mData="+tf);
         }
     }
     tableConfig.aoColumns.push (co);

     if ($(this).data ('direction')) {
         tableConfig.aaSorting.push ([index, $(this).data ('direction')]);
     }
});

using

<thead>
<tr>
    <th data-mdata="euroDate" data-sortable="true">Date</th>
    <th data-mdata="ignoreColumnAccents" data-sortable="true">Immeuble</th>
    <th data-mdata="ignoreColumnAccents" data-sortable="true">Utilisateur</th>
    <th data-mdata="ignoreColumnAccents" data-sortable="true" class="hidden-xs hidden-sm">Terminal</th>
    <th data-mdata="ignoreColumnAccents">Machine</th>
    <th>Durée</th>
    <th>Montant</th>
</tr>
</thead>

OK, I'll read it!

@allan : that doesn't work, that's why I'm trying to figure out how to do!

allan

I would strongly recommend you use columns.render and not columns.data (or mData in old terminology). Using columns.data as a function can get really complicated.

CyrilF

OK thank you. You said mData in old terminology so you mean that I should rewrite initDataTableHelper() with the new API?

allan

I would suggest using the 1.10 style if you are using 1.10. A convert guide is available.

It don't fix the issue though - as I say, use columns.render, not columns.data (regardless of if you are using the old or new style).

Allan

jr42.gordon

I would recommend using "columns.data" option if all you are doing is displaying/manipulating a single variable data within an object.

I would recommend using "columns.render" option you require additional content within the cell, in addition to the associated object variable data, or if your display/filter/sort values are all different.

Viewing the links provided by Allan will give you good examples to use.