Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DataTables - Dynamic Columns From Ajax Data Source?

I am trying to get DataTables to read the column names from an AJAX data source but it seems that there must be something that I must be missing here.

I made a fiddle fiddle in which I can manually define the data and columns that are being used by the table.

The table is declared in the HTML and there is no no need to define the column names (<thead>..</thead>):

<table id="example" class="display table table-striped table-bordered" 
       cellspacing="0" width="100%"></table>

In the JS we manually define the data:

var data = [
    [ "Row 1 - Field 1", "Row 1 - Field 2", "Row 1 - Field 3" ],
    [ "Row 2 - Field 1", "Row 2 - Field 2", "Row 2 - Field 3" ],
];

Then manually define the column names or titles:

var columns = [
    { "title":"One" },
    { "title":"Two" }, 
    { "title":"Three" }
];

Then when we initialise the table we simply pass the previously declared information across for DataTables to use:

$(document).ready(function() {
  $('#example').DataTable( {
    dom: "Bfrtip",
    data: data,
    columns: columns
  });
});

Which results in:

Resulting Table

Now my question is how would I get this to work if the data is included in the AJAX server side response?

I have tried this in various ways and forms but nothing really seems to work out here and I am battling to find relative documentation on this.

For example if the server side processing sent back a JSON response which includes the column names at the end:

{
  "data": [
    {
      "id": "1",
      "One": "Row 1 - Field 1",
      "Two": "Row 1 - Field 2",
      "Three": "Row 1 - Field 3"
    },
    {
      "id": "2",
      "One": "Row 2 - Field 1",
      "Two": "Row 2 - Field 2",
      "Three": "Row 2 - Field 3"
    }
  ],
  "options": [],
  "files": [],
  "columns": [
    {
      "title": "One",
      "data": "One"
    },
    {

      "title": "Two",
      "data": "Two"
    },
    {
      "title": "Three",
      "data": "Three"
    }
  ]
}

Given this is the response, I tried to configure DataTables to use an AJAX data source for the row information as follows:

$(document).ready(function() {
  $('#example').DataTable( {
    dom: "Bfrtip",
    "ajax": '/test.php',
    columns: columns
  });
});

But obviously columns is undefined here.

So I get the column data before hand:

function getPromise() {
  var deferred = $.Deferred();
  var dataUrl = document.location.origin+'/text.php';
  $.getJSON(dataUrl, function(jsondata) {
    setTimeout(function() {
      deferred.resolve(jsondata);
    }, 0);
  }).fail(function( jqxhr, textStatus, error ) {
    // ********* FAILED
    var err = textStatus + ", " + error;
    console.log( "Request Failed: " + err );
  });
  return deferred.promise();
}
// Get the columns
getPromise().done(function(jsondata) {
  columns = jsondata.columns;
  console.log(columns);
});

And pass it to DataTables as above. But this time all I get when running the example is an error in the console saying TypeError: p is undefined.

So then how could I make use of the dynamically generated columns that are being returned within the server side response? Is there not a simpler way to achieve this?

EDIT:

DataTables Editor code for server side processing / to generate the JSON response mentioned above:

<?php
// DataTables PHP library
require_once '/path/to/DataTables.php';

// Alias Editor classes so they are easy to use
use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$out = Editor::inst( $db, 'example' )
  ->fields(
    Field::inst( 'id' )->set(false),
    Field::inst( '`One`' )->validator( 'Validate::notEmpty' ),
    Field::inst( '`Two`' )->validator( 'Validate::notEmpty' ),
    Field::inst( '`Three`' )->validator( 'Validate::notEmpty' )
  )
  ->process( $_POST )
  ->data();

// On 'read' remove the DT_RowId property so we can see fully how the `idSrc`
// option works on the client-side.
if ( Editor::action( $_POST ) === Editor::ACTION_READ ) {
    for ( $i=0, $ien=count($out['data']) ; $i<$ien ; $i++ ) {
        unset( $out['data'][$i]['DT_RowId'] );
    }
}

// Create the thead data
if (count ($out) > 0) {
  $columns = array();
  foreach ($out['data'][0] as $column=>$relativeValue) {
    // Add all but the id value
    if ($column !== 'id') {
      // Add this column name
      $columns[] = array(
        "title"=>$column,
        "data"=>$column
      );
    }
  }
}
// Add the the thead data to the ajax response
$out['columns'] = $columns;
// Send the data back to the client
echo json_encode( $out );
like image 786
Craig van Tonder Avatar asked Mar 16 '16 20:03

Craig van Tonder


People also ask

How do you get the dynamic column header and result from Ajax call in jquery DataTable?

how to get the dynamic column header and result from ajax call in jquery datatable. var $table=$('#MSRRes'). dataTable( { "bFilter": false, "bDestroy": true, "bDeferRender": false, "bJQueryUI": true, "oTableTools": { "sSwfPath": "swf/copy_cvs_xls_pdf.

What is FixedColumns in DataTables?

FixedColumns provides the option to freeze one or more columns to the left or right of a horizontally scrolling DataTable. This allows information in the fixed columns to remain visible even when scrolling through the data set. This can be particularly useful if you wish to show a large number of columns.

What is Deferrender in DataTables?

This option allows DataTables to create the nodes (rows and cells in the table body) only when they are needed for a draw.

What is Serverside in DataTables?

DataTables' server-side processing mode is a feature that naturally fits with Scroller. Server-side processing can be used to show large data sets, with the server being used to do the data processing, and Scroller optimising the display of the data in a scrolling viewport.


1 Answers

If you don't use the built in DataTables ajax it should be easy enough given the structure of your data:

$(document).ready(function() {
    $.ajax({
        type: 'POST',
        dataType: 'json',
        url: '/echo/json/',
        data: {
            json: JSON.stringify(jsonData)
        },
        success: function(d) {
            $('#example').DataTable({
                dom: "Bfrtip",
                data: d.data,
                columns: d.columns
            });
        }
    });
});

Like this JSFiddle, you're limited then to loading all the data at once but that shouldn't be a huge issue... unless you alter it get the columns from the initial ajax call and once the DataTable is initiated then add the built-in ajax - I've not tried this though...

like image 98
annoyingmouse Avatar answered Oct 01 '22 05:10

annoyingmouse