CODEWITHSUNDEEP
jquerysqldatatables
JTFRage
JTFRage

Reputation: 397

Update DataTable based on new object data

I'm having some problems updating a datatable with new values. Currently, I am calling an API that returns columns and rows for a particular query. I then feed that information into the datatable (see below).

Example: select * from parms

{columns: Array(4), data: Array(3)}

4 columns and 3 rows returned from the query. I now feed that into the data and columns property of the datatable (below).

<table class="table table-sm table-bordered table-striped bg-white" id="QueryResults"></table>

$('#QueryResults').dataTable({
  dom:'l<Bf<t>ip>',
  lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
  destroy: true,
  "data": dataObject.data,
  "columns": dataObject.columns
});

The following table is then produced.

enter image description here

This is all good if it's the initial query upon instantiation. However, every subsequent query that has a different number of columns doesn't show properly or the datatable doesn't work.

So let's say I selected * from this table, but now I only want to see PARMID.

select parmid from parms

Now executing this query after having done the one above, displays the table as seen below:

enter image description here

Even though the query only returned one column: {columns: Array(1), data: Array(3)}

I do believe that I have to destroy the table before creating a new one. But after I do, I can no longer create a table since the div has been removed from the DOM. Being able to do this with datatables should be pretty easy, but I can't find what I'm looking for on their documentation site. I'd like the datatables to update automatically based on the new data that has been returned from the API call.

I can't pin-point what I'm doing wrong. Any help would be appreciated.

Thanks!

Upvotes: 1

Views: 876

Answers (1)

andrewJames
andrewJames

Reputation: 22012

As well as destroying the table, you need to remove any contents in the <html> element.

This demo uses a button to toggle between two different data sources, with different numbers of rows and columns.

The toggle button and table:

<div style="margin: 20px;">
    <button id="toggler" type="button">Toggle Data</button>
    <br><br>
    <table id="example" class="display dataTable cell-border" style="width:100%"></table>
</div>

The DataTables script and button click event:

<script>

var dataObjectOne = {
  data: [ 
    { "name": "Airi Satou", "position": "System Architect", "salary": "$320,800" },
    { "name": "Bruno Nash", "position": "Accountant", "salary": "$170,750" } 
  ],
  columns: [ 
    { title: "Name", data: "name" },
    { title: "Position", data: "position" },
    { title: "Salary", data: "salary" }  
  ]
};

var dataObjectTwo = {
  data: [ 
    { "name": "Tiger Nixon", "office": "Edinburgh" },
    { "name": "Cedric Kelly", "office": "Edinburgh" },
    { "name": "Garrett Winters", "office": "Tokyo" } 
  ],
  columns: [ 
    { title: "Name", data: "name" },
    { title: "Office", data: "office" }
  ]
};
 
var dataObject = dataObjectOne;
var tableSpec = { 
  "dom": 'l<Bf<t>ip>',
  "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
  "data": dataObject.data, 
  "columns": dataObject.columns
};
  
$(document).ready(function() {
 
  var table = $('#example').DataTable( tableSpec );

  const button = document.querySelector('button');

  button.addEventListener('click', event => {
    dataObject = (dataObject === dataObjectOne) ? dataObjectTwo : dataObjectOne;
    
    table.destroy();
    
    // Force the removal of table contents (thead, tbody):
    document.getElementById("example").innerHTML = '';
    
    // load the new row and column data
    tableSpec.data = dataObject.data;
    tableSpec.columns = dataObject.columns;
    
    table = $('#example').DataTable( tableSpec );    
  });

} );

</script>

To avoid having to define the DataTable options twice, I declare them all in a variable tableSpec. I can then place either of my two data sources into this tableSpec.

Upvotes: 0

Related Questions