Dropdown Column with Ajax

I want to create dropdown column from my ajax source but I couldn't get it done I try everything.

http://live.datatables.net/hexidaqi/1/edit

In the example, I tried to have the office column in the dropdown.

Can you help with me this one please :(

Solution

You can't use a column renderer in the way that you are trying to use it. This renderer only has access to the data in each separate row. It does not have a complete view of all the rows in the table. So it can't see what office data has already been processed, or will be processed.

To work around this, there are 2 approaches that I can think of (and probably more that I cannot think of):

Pre-process your ajax JSON so that you have already built a list of the unique office names, and add that to each object in your JSON (or similar) so that this data is now available to your column renderer.
Wait until the table has finished being created (with no column renderer and no select lists) - and then build the select lists from the data in the table, and replace the office values with the select lists.

Here is that second approach:

$(document).ready(function() {

var table = $('#example').DataTable( {
  ajax: {
    url: "my test URL here - see the JSON data below"
  },
  searching: "false",
  columns: [
    { data: "name" },
    { data: "position" },
    { data: "office" },
    { data: "age" },
    { data: "start_date" },
    { data: "salary" }
  ],
  initComplete: function(settings, json) {
    var officeData = this.api().column(2).data();
    var officeSelect = $('<select><option value=""></option></select>');
    officeData.unique().sort().each( function ( d, j ) {
      officeSelect.append( '<option value="' + d + '">' + d + '</option>' );
    } );
    officeSelect.appendTo( $('table#example tbody td:nth-child(3)').empty() );
  }

} ); 

} );

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
          <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
          </tr>
        </thead>
    </table>

</div>



</body>
</html>

After the table has finished being built, we can access the data in column index 2 (the Office column):

var officeData = this.api().column(2).data();

We can then process each unique value and create a select list <option>:

officeData.unique().sort().each( ... );

After building the HTML for the select list, we can replace all the Office column's nodes with this new select list:

officeSelect.appendTo( $('table#example tbody td:nth-child(3)').empty() );

Note that the DataTables column indexing starts at zero, but the jQuery :nth-child selector starts at 1 - which is why we have column(2) but td:nth-child(3).

This is a basic solution - for example, there aren't any event handlers attached to the select lists. If you want to actually do something useful with selections made by a user, then that would be extra work (and probably a new question).

For completeness, here is my source JSON:

{ "data": [
    {
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "age": "23"
    },
    {
      "name": "Garrett Winters",
      "position": "Accountant",
      "salary": "$170,750",
      "start_date": "2011/07/25",
      "office": "Tokyo",
      "age": "34"
    },
    {
      "name": "Ashton Cox",
      "position": "Junior Technical Author",
      "salary": "$86,000",
      "start_date": "2009/01/12",
      "office": "San Francisco",
      "age": "45"
    },
    {
      "name": "Cedric Kelly",
      "position": "Senior Javascript Developer",
      "salary": "$433,060",
      "start_date": "2012/03/29",
      "office": "Edinburgh",
      "age": "36"
    },
    {
      "name": "Airi Satou",
      "position": "Accountant",
      "salary": "$162,700",
      "start_date": "2008/11/28",
      "office": "Tokyo",
      "age": "42"
    }
] }

Update

Based on the example provided in one of the comments below:

That is a clever approach, but it does seem to be over-complicated for the problem you are trying to solve - and, as you point out, it assumes each value is unique across the entire set of values in the column.

Here is a revised approach which does a couple of new things from my original approach:

It operates on DataTable nodes, so that the values are available across all pages.
It captures the value provided in the source data and selects that as the "selected" (displayed) value in the drop-down:

$(document).ready(function() {

var table = $('#example').DataTable( {
  ajax: {
    url: "http://localhost:7000/ajax-employees"
  },
  searching: "false",
  "pageLength": 2, // just for testing multiple pages
  columns: [
    { data: "name" },
    { data: "position" },
    { data: "office" },
    { data: "age" },
    { data: "start_date" },
    { data: "salary" }
  ],
  initComplete: function(settings, json) {
    var officeData = this.api().column(2).data();
    var officeNodes = this.api().column(2).nodes().toArray();
    var officeList = officeData.unique().sort().toArray();

    officeData.each( function ( cellValue, idx ) {
      var options = '';
      officeList.forEach((office) => { 
        console.log( cellValue, office );
        if (office === cellValue) {
            options = options + '<option value="' + office + '" selected>' + office + '</option>';
        } else {
            options = options + '<option value="' + office + '">' + office + '</option>';
        }
        console.log( options );
      } );
      var officeSelect = $('<select>' + options + '</select>');
      officeSelect.appendTo( $(officeNodes[idx]).empty() );
    } );
  }

} ); 

} );

By capturing the nodes in the Datatable:

this.api().column(2).nodes()

...we can build drop-downs for all rows in the table, regardless of pagination.

By comparing the cell's value against the master list of unique offices, we know when to add the selected attribute to an <option> element.