trouble rendering a dynamic recordset using Ajax

joannaz

I need to render a table that gets data from a postgres server using Ajax and a php function. This table has an unknown number of columns and rows. It may also have no data. The names of the columns differ for each dataset.
What is the best way of displaying this data and setting up the dataTable? I can extract the data from the database as (a) an associative array or (b) a unnamed 2D array where the first row is made up of the column names of the data.
My problem is that I can't seem to find a function definition that will render the data, as I can't predefine the columns.

My (non-working) js currently looks like this for data formatted as option (b):

!function  GetFileAttributes() {
        var dsid = GetTableRowID();

        if (Boolean(dsid) == false)
        {
            dsid = 0;
        }
        ;  //if no row is selected, set dsid to 0.  

        console.log("Getting multi file attributes");

        //setup table for all of the details for Multi-file datasets
        $('#tblMFA').DataTable({
            ajax:
                    {
                        url: siteURL + "/?action=populate_fileattributes_table&dsid=" + dsid,
                dataSrc: ""
                    },
            search: false,
            autoWidth: false,
            ordering: false,
            scrollX: true,
            scrollY: "1000px",
            PaginationType: 'bootstrap',
            scrollCollapse: true,
            paging: true,
            info: false,
            searching: false,
            stateSave: false,
            language: {
                lengthMenu: "",
                zeroRecords: "No data available",
                infoEmpty: "No file selected"
            },
            drawCallback: function ( ) {
                $.fn.dataTable.tables({visible: true, api: true}).columns.adjust();
            }
        });

        var table = $('#tblMFA').DataTable();
        table.column(0).visible(false);
    }();

Option (b) (when not an empty recordset) displayed on the url shown is:
[ [ "dsid", "Researcher code", "Filename", "Have File?", "Pump number", "Test number", "Test number order", "BEP", "Flowrate in m3\/hr", "Velocity in m\/s", "%BEP", "Efficiency", "Power to pump in kW", "Calculated Efficiency", "NPSHa", "NPSHratio", "NHSPR", "Total head in m", "Suction pressure in kPa", "Vapor pressure in kPa", "Cavitation number" ], [ "9", "28-018", "2_1", "1", "2", "1", "0", "186", "50", "0.7", "0.27", "39", "52.2", "38.588", "9.67", "8.13", "1.19", "144.63", "3", "3.78", "-2.8" ], [ "9", "28-022", "2_2", "1", "2", "1", "1", "186", "50", "0.7", "0.27", "39", "52.2", "38.58", "4.87", "4.09", "1.19", "144.6", "-57.3", "3.78", "-220.15" ], [ "9", "28-023", "2_3", "1", "2", "1", "2", "186", "50", "0.7", "0.27", "39", "52.2", "38.564", "4.13", "3.47", "1.19", "144.54", "-78.7", "3.82", "-297.44" ], [ "9", "28-024", "2_4", "1", "2", "1", "3", "186", "50", "0.7", "0.27", "39", "52.2", "38.688", "1.95", "1.64", "1.19", "145.01", "-86.4", "4.34", "-327.06" ], [ "9", "28-025", "2_5", "1", "2", "1", "4", "186", "50", "0.7", "0.27", "39", "52.2", "38.356", "1.19", "1", "1.19", "143.75", "-86.9", "4.27", "-328.59" ] ]

Any thoughts would be greatly appreciated as I'm really stuck.

Thanks in advance.

kthorngren

If you want to create the columns dynamically you will need to use an external ajax call instead of ajax. Here is an example you can start from. It is pulling the columns names from the data object but you could return an additional object with the names, etc.

http://live.datatables.net/fafuyeyu/55/edit

Kevin

joannaz

Thanks Kevin. That seems to be on the right track, but it seems to only run at the time of table initialisation. I can't find a way of updating the columns and data properties of the object after it has been initialised.

What command do I use to set the columns or data property after initialisation?

I can get the new dataset and determine the column names (my dataset is a json encoded object) but I can't seem to update my DataTable with the new info.

kthorngren

destroy() is used when the config options of the Datatable need to be changed.

Kevin

joannaz

@kthorngren

Thanks. I guessed as much.

Again, I'm on the right track, but still can't quite get it working reliably...

My code is available here:

https://prognosticsdl.ecm.uwa.edu.au/pdl/

The problematic table is #tblMFA and is shown on the 'Multi-file Parameters' tab. Of the datasets shown only Pump2_Cavitation_RawAccData has any data to populate this table.

My current issues are:

(a) although I can get the list of column names from the datasource and the data is displaying, the full list of column names are not. They will only display if the first time I create the object I fill table column definition with an empty array that has the right number of rows. (e.g. [,,,,,,,,,,,,,,,])

(b) I usually (but not always) get the following error:
"DataTables warning: table id=tblMFA - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4"
But It should never be looking for parameter '0'. I think this error is coming up about the time the table is being destroyed and recreated.

Any help that can be offered would be greatly appreciated.

Thanks.

joannaz

@kthrongren

Thanks for all the help so far.

I've made lots of progress since the last update, but I'm still only showing the first column title.

I have implemented all the suggestions you've made in this post and similar posts on this subject. I am reading the data ok and building the array of column titles ok. Then I destroy the old table before reinitialising it with the new column definition.

However, I am still only displaying the first column's title whilst all the rest are blank.

My js is now:

    $('#tblDSResults').on('click', 'tr', function () {
            var id = GetTableRowID();
            var table6 = $('#tblMFA').DataTable();
            
            if (Boolean(id) == false)  id = 0;
                    
            table6.ajax.url(siteURL + '/?action=populate_fileattributes_table&dsid=' + id).load();
            
            getMFData(id,function(mydata) {
                var mycolumns = [];
                var mycolnames=[];
                var colHdr="";
                console.log(mydata.length);
                
                    if (mydata.length > 20){ //20 is the length of the object with no data in it.
                        mydata = JSON.parse(mydata);    
                            
                            //console.log(mydata[0]);
                            
                            if (Object.keys(mydata.data[0]).length > 0 ) 
                            {
                                columnNames = Object.keys(mydata.data[0]);
                                
                                for (var i=0;i<columnNames.length; i++) 
                                {
                                    mycolumns[i]={data: columnNames[i], title:columnNames[i]};
                                    mycolnames[i]={targets:[i],title:columnNames[i]};
                                  //colHdr=colHdr + "<th>" + columnNames[i] + "</th>";
                                }
                            } else { 
                                mycolumns = [{data:null}]; 
                                mycolnames = [{targets:'_all', title: ''}];
                            }   
                        
                        //console.log(colHdr);
                        //document.getElementById("#tblMFAHeader").innerHTML=colHdr;
                    } else { 
                        mycolumns = [{data:null}]; 
                        mycolnames = [{targets:'_all', title: ''}];
                    }
                
                console.log(mycolnames);
                
                table6.destroy();
                
                table6=$('#tblMFA').DataTable({
                    ajax: {
                      url: siteURL + "/?action=populate_fileattributes_table&dsid=" + id,
                      dataSrc: "data"
                    },
                    columns: mycolumns,
                    columnDefs:mycolnames,
                    deferRender:true,
                    paging:true,
                    pageLength:20,
                    fixedHeader:true,
                    searching: false,
                    autoWidth: false,
                    ordering: false,
                    scrollX:true,
                    scrollY:false,  
                    language: {
                        lengthMenu: "",
                        zeroRecords: "No data available"
                    },
                    drawCallback: function ( ) {
                        $.fn.dataTable.tables({visible: true, api: true}).columns.adjust();
                    }
                    
                });
                console.log("tblMFA Recreated"); 
                console.log(table6.columns());
            
                
            }) 
});

Why would only the first column be rendered to the screen? It's also sometimes not being deleted when I change the dataset to view another dataset with no data for this table but this behaviour is somewhat variable (sometimes it deletes the first column title and sometimes it doesn't).

I've tried updating the column, columnDefs separately as well as both together and this makes no difference.

This issue can be viewed on https://prognosticsdl.ecm.uwa.edu.au/pdl/
Click on the Pump2_Cavitation_RawAccData dataset in the top table and the problem is visible in the table on the Multi-file attributes tab.

I've updated to the following version of datatables files:

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4/dt-1.10.18/fc-3.2.5/fh-3.1.4/r-2.2.2/sc-1.5.0/sl-1.2.6/datatables.min.css"/>

<script type="text/javascript" src="https://cdn.datatables.net/v/bs4/dt-1.10.18/fc-3.2.5/fh-3.1.4/r-2.2.2/sl-1.2.6/datatables.min.js"></script>

Should I buy support credits to get this sorted? I am happy to, if it means I'll get a solution to this problem.

Thanks in advance.

kthorngren

I'm not sure why you are creating a Datatable in line 3, using ajax.url().load()` in line 7 then destroying and creating a new Datatable using what looks like the same URL. Seems like you are requesting the data twice.

The problem with building the columns is likely due to ajax being an async process meaning the ajax request is still processing when you hit the code to build the columns. You will need to use a callback for your ajax request. The callback will build your columns and initialize Datatables.

You can use the success function for this like this example:
http://live.datatables.net/huyexejo/1/edit

It uses jQuery's ajax and the success function to build the columns definition and applies the ajax response data using data.

Kevin

joannaz

Thanks Kevin. I did as you suggested but am getting the same result.

This is my code:

$(document).ready(function () {
    // Start writing your custom functions here.
    // All the necessary pluigns are already loaded.

    !function PrepareFileList() {
        
        $('#tblDSResults').DataTable({
            ajax: {
                url: siteURL + "/?action=populate_files_table",
                dataSrc: ""
            },
            columns: [
                {title: "Dataset name", data: 0, width: "60%"},
                {title: "Filetype", data: 1, width: "10%"},
                {title: "Fileset size (kB)", data: 2, width: "10%"},
                {title: "Average rating", data: 3, width: "10%"},
                {title: "DSID", data: 4, visible: false}
            ],
            autoWidth: true,
            ordering: true,
            order: [[2, 'dsc'], [0, 'asc']],
            scrollY: "400px",
            scrollCollapse: true,
            hover: true,
            select: {
                item: 'row',
                style: 'single',
                info: false
            },
            language: {
                search: 'Filter these records:',
                info: "_TOTAL_ datasets found : Showing datasets _START_ to _END_ ",
                lengthMenu: 'Display <select>' +
                        '<option value="10">10</option>' +
                        '<option value="20">20</option>' +
                        '<option value="50">50</option>' +
                        '<option value="-1">All</option>' +
                        '</select> datasets'
            }
        });
    }();


    !function PrepareTabs() {
        $(".nav-tabs a").click(function () {
            $(this).tab('show');
            $.fn.dataTable.tables({visible: true, api: true}).columns.adjust().draw();
            $.fn.dataTable.tables({visible: true, api: true}).responsive.recalc();
            console.log("click tab nav")
        });
    }();

    !function OnRowClick() {    
        $('#tblDSResults').on('click', 'tr', function () {
            var id = GetTableRowID();
            if (Boolean(id) == false)  id = 0;
            var table6 = $('#tblMFA').DataTable();
            
table6.destroy;
            
            getMFData(id);
            
        });
    }();

   function GetTableRowID() {
        var table = $('#tblDSResults').DataTable();
        var id = table.cell(table.row('.selected'), 4).data();
        if (Boolean(id) == false)
            {
                id = 0;
            };
        console.log("dsid=" + id);
        return id;
    };

    function getMFData(dsid) {
        var columns = [];
        
        //setup table for all of the details for Multi-file datasets
    
        $.ajax({
          url: siteURL + '/?action=populate_fileattributes_table&dsid=' + dsid,
          dataType: 'json',
          success: function (data){
            var data = data.data;
            console.log("rows of data =" + data.length);
            if (data.length === 0) { //an empty recordset
                columns = [null];
                data=[];
                } else {                                    
                    var columnNames = Object.keys(data[0]);
                    for (var i in columnNames) {
                        columns[i]={
                            title: columnNames[i],
                            data: columnNames[i]
                        };                      
                    }
                }
            console.log(siteURL + '/?action=populate_fileattributes_table&dsid=' + dsid);   
            console.log(data);
            console.log(columns);
            
            $('#tblMFA').DataTable({
                data:data,
                columns:columns,
                language: {
                    lengthMenu: "",
                    zeroRecords: "No data available"
                        },
                paging:true,
                scrollX:true,
                scrossY:true,
                searching: false,
                autoWidth: false,
                ordering: false /*,
                drawCallback: function ( ) {
                    $.fn.dataTable.tables({visible: true, api: true}).columns.adjust();
                    }   */
                }) ;                        
            },
            error: function(){
                console.log("problem here");
                }
        });
    };

      
   !function  GetFileAttributes() {
        var dsid = GetTableRowID();
        if (Boolean(dsid) == false)
        {
            dsid = 0;
        }
        ;  //if no row is selected, set dsid to 0.  

        console.log("Setting up multi file attributes for the first time");
        //setup table for all of the details for Multi-file datasets
         
        getMFData(dsid);

        
    }();
});

How I want the page to behave:

When the page is first created most of my tables (all but one in fact) have no data and the page has to manage this case and render the DataTable objects with no data. If the user clicks on an option in #tblDSResults (let's call it Table 1) that has data for my #tblMFA Datatable (aka Table 6) then this new data needs to be shown and the column titles updated. When the user deselects the row in Table 1, the data in Table 6 needs to disapper and the 'empty dataset' comment is returned. Similarly, if the user then clicks on a different option in Table 1, Table 6 needs to be updated with the new data (and column names) or empty the table.

What it does:

Datatable Table 6 is initialised correctly with no data the first time.
I click on the option in Table 1 that I know has data for Table 6.
Table 6 is filled with data and the first column's title is updated.
No other column titles are updated.

When I deselect the option in Table 1, nothing happens.
I get the following error: "Uncaught TypeError: Cannot read property 'mData' of undefined".
Data=[null] and Columns = [].
this is exactly the case when I first initialised the table with no data, but this time I get an error.

When I select any other option in Table 1 that has no data for Table 6, I get the same error.

When I return to the option in Table that has data for Table 6, I get the following errors:
Cannot read property 'parentNode' of null for the following line:
$('#tblMFA').DataTable().destroy();

joannaz

Sorry, pressed post by accident. This is continued from above:

It looks like the destroy function is not working properly. It isn't allowing the replacement table to be initialised fully.

This is why the examples you provide work ok if the data is available to initialise the table in the first instance. If I force the initial case to read data that isn't null, everything works for that case.

But as soon as I destroy and reinitialise the table with the new properties, the column titles are ignored and the new (null) dataset can't be read.

Any more ideas?

allan

Here is how I would approach what you are doing:

1. I'd drop the table6 stuff in the OnRowClick function. Move everything for the the MF Parameters table into the getMFData() function.
2. Destroy and empty the existing DataTable if there is one
3. Check to see if there is any data - if not, hide the table and show a message saying there is no data (no point in showing a DataTable since we can't derived column information).
4. If there is data, build the columns array and then the DataTable.

This is the code that I've just put together for this:

function getMFData(dsid) {
  //setup table for all of the details for Multi-file datasets
  $.ajax({
    url: siteURL + "/?action=populate_fileattributes_table&dsid=" + dsid,
    dataType: "json",
    success: function(data) {
      var data = data.data;

      // Check if there is an existing DataTable and if so, destroy it
      if ($.fn.dataTable.isDataTable("#tblMFA")) {
        $("#tblMFA")
          .DataTable()
          .destroy();
        $("#tblMFA").empty();
      }

      if (data.length === 0) {
        // No data, so show a message to that effect
        $("#tblMFA")
          .css("display", "none")
          .after('<div class="no-data">No data currently available</div>');
      } else {
        // Remove any `no-data` message from a previous table
        $("#tblMFA")
          .parent()
          .children("div.no-data")
          .remove();

        // Build the DataTables columns object from the property names in the data object
        var columns = Object.keys(data[0]).map(function(val) {
          return {
            title: val,
            data: val
          };
        });

        $("#tblMFA").DataTable({
          data: data,
          columns: columns,
          language: {
            lengthMenu: "",
            zeroRecords: "No data available"
          },
          paging: true,
          scrollX: true,
          searching: false,
          autoWidth: false,
          ordering: false
        });
      }
    },
    error: function() {
      console.log("problem here");
    }
  });
}

Allan

joannaz

Thanks Allan.

I replaced my function with yours and updated the https://prognosticsdl.ecm.uwa.edu.au/pdl/.

This got rid of all the error messages but it is now displaying neither the data nor the column titles. The table is being generated correctly and it knows about the data because it is showing the number of records and paginating it correctly, but no data or titles are shown. I can see the columns and data in the consolelog, but not on the webpage.

I realise you didn't change that bit of my code, but somehow the bit that you did change has affected the rendering of the data.

Did I need to do something else other than steps 1-4?

Also, multiple "The no data available line" were being added if consecutive options with no data were being selected. So I now delete this each time before adding it when required.

Thanks in advance.

joannaz

Progress!

To make this work I needed to delete the '.css("display", "none")' from line 20 of your code. What was the purpose of this?

I have one last issue then, to apply this approach to another table for which I need 2 fixed columns.

When I run this code with fixedColumns the behaviour is quite erratic. Sometimes the data is updated, and sometimes it isn't. When it isn't the log shows a 'Cannot read property 'dt' of undefined' type error when trying to execute the destroy function.

Is this a timing issue? Could it be rectified by putting in a delay somewhere?

Thanks again, for your help.

allan

Oops - sorry! Add:

$("#tblMFA").css('display', 'block');

On line 36 in the above code (i.e. before the DataTable initialisation). That should fix it - I forgot to unhide the hidden table!

Allan

joannaz

Thanks Allan (and Kevin before that). That did it!
Any thoughts about the fixedColumns issue? Why would the behaviour be so erratic, sometimes showing the correct data and sometimes flagging an error instead?

Joanna

allan

Sorry I missed that point! I've just had a bit of a play around on the page and I haven't been able to reproduce the issue. Are you able to give me instructions on what I need to do in order to make it occur?

Thanks,
Allan

joannaz

Hi Allan,

Sorry for the delay. You wouldn't have seen that behavior as I switched off the feature. I've put it back on again and you can see the problem on the Files Column Details tab. This is just a replication of the code you previously fixed but with the addition of fixedColumns: { leftColumns:2} to the table initialization. I can't keep this version up for long, as it stops the data being displayed, so please let me know when you are done.

My full initialization is:

$("#tblColumnInfo").DataTable({         
                data:data,
                columns: columns,
                language: {
                  lengthMenu: ""
                },
                paging: true,
                pageLength:20,
                scrollX: true,
                scrollY:true,
                fixedColumns: {
                    leftColumns:2
                },
                searching: false,
                autoWidth: false,
                ordering: false,
                drawCallback: function() {
                  $.fn.dataTable.tables({visible:true,api:true}).columns.adjust();
                }

When I switch off fixed Columns it behaves as expected.

Thanks in advance.
Joanna