Dynamic Columns From Ajax Data Source?

Dynamic Columns From Ajax Data Source?

icdebicdeb Posts: 20Questions: 5Answers: 0
edited March 2016 in Free community support

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:

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 );

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    There is currently no option to get the columns using the ajax option (FAQ). Instead, what you have to do is make the Ajax request yourself (using $.ajax or anything else) and get the JSON data that way. Then you can use exactly the same method as your working example above - use data and columns with their values assigned from the Ajax data source.

    Regards,
    Allan

  • movilmovil Posts: 2Questions: 0Answers: 0

    Hello Allan,
    We made a simple example with our solution and posted it here:

    https://github.com/martinberlin/datatables-dynamic-columns

    We are happy to share in case someone finds it useful.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Awesome - thanks for sharing that with us!

    Allan

This discussion has been closed.