Initialize a table with a dynamic number of columns?

janetcl

Is it possible to initialize a table with a dynamic number of columns (and variable titles for the columns)? I am loading in data from a mongodb collection which represents products being sold by different vendors. The number of vendors (and their names) is dynamic, so there is no way for me to specify what they may be in advance. My app.js function is this:

app.get('/products', (req, res) => {
    db.collection("Output").find().toArray((err, result) => {
        if (err) return console.log(err);
        console.log('got product collection');
        console.log(result);
        res.send(result);
    });
})

The Output collection contains the aggregation of all of the collections. Previously, when I had a static number of columns with known titles, I used this to initialize my Datatable:

$.getJSON('/products', function(data){
              table = $("#myTable").DataTable({
                  "data": data,
                  "columns": [
                  { "data": 'name'},
                  { "data": 'file_id',
                    "defaultContent": "No image",
                    "visible": false},
                  { "data": 'barcode',
                    "visible": false},
                  { "data": 'Masivos.price',
                    "defaultContent": "N/A",
                    "className": "cdoproducts" }
                   );

Note that I had a lot more columns and column definitions. In the body of the HTML code, I specified the header and footer column names as follows:

<thead class="textwrap">
                        <tr>
                            <th><h3>Producto</h3></th>
                            <th><h3>Image</h3></th>
                            <th><h3>Barcode</h3></th>
                            <th><h3>Masivos</h3></th>
                    </thead>
                    <tfoot>
                        <tr>
                            <th>Producto</th>
                            <th>Image</th>
                            <th>Barcode</th>
                            <th class="cdofooter">Masivos</th>
                    </tfoot>
                </table>

How can I do this when the number of columns (and their titles) are dynamically generated by the server?

colin

Hi @janetcl ,

Will the data be on the page already (along with the table), or do you have to create the table too? Or will you retrieve the data from Ajax? In your previous initialisation, you were passing in "data": data,, or will that still be the case?

Cheers,

Colin

kthorngren

Here is an example that you can start from. The example pulls the column names from the returned data object but you can get them from any object in your ajax response.

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

Kevin

janetcl

Thanks for the help. I am trying to use ajax now. Kevin, for the sample, do you have the objects.txt file? I am trying to figure out how to format my data, since much of the data is nested objects.

kthorngren

do you have the objects.txt file?

That example is based off the live.datatables.net ajax loaded objects.txt example:
https://datatables.net/manual/tech-notes/9

This is the base example:
http://live.datatables.net/ajax-objects/1/edit

What I've seen other people do in their production code is return an object that specifically contains the column info. For example:

{
  "data": [ {....}, {....}...],
  "columns": [{....}, {....}...]
}

But your server script would need to generate the columns object. Lots of options here

Kevin

janetcl

Great, that works! I decided to use an external array to store collection names. Thanks so much.

I am having another issue now -- in my object.txt file, not every single object has all of the fields that will become columns. In other words, not every product is sold by every single provider. Based on the way that my data is loaded into MongoDB, the key and value pairing for providers does not exist for a product if the provider does not have it. So, each object has a varying number of fields (which are all nested with more fields inside of them). How can I make DataTables put down a default value even if the data field is null? I used 'defaultContent:' in the past, but that was when the field existed and was set to null. Currently I am getting this error in my console:

datatables.js:5899 Uncaught TypeError: Cannot read property 'aDataSort' of undefined

Please let me know if you'd like clarification, I know this can be quite confusing.

colin

Hi @janetcl ,

When DataTables gets the data, it will expect all rows to have the number of columns determined by the table. If your data doesn't match that, you'll need to pre-parse it and fill in the blanks.

If you're loading the data outside of DataTables and just passing in an array/object collection, then you could do this at any point before the table inititialisation. If the data is being Ajax loaded by DataTables, you could modify the returned data before it's loaded into the table in xhr or ajax.dataSrc,

Hope that helps,

Cheers,

Colin

janetcl

Perfect! Thank you so much, Colin! I decided to pre-parse the data beforehand. I am also trying to manipulate which data is shown / hidden (and format the columns in general) using ColumnDefs, and I understand that you can refer the targets by the column index or a string indicating the class. How can I refer to a specific column in ColumnDefs when I am inputting my table with a dynamic number of columns, as shown above?

janetcl

Additionally, if I am to pre-parse the data beforehand, but my columns are nested within an array of objects, how can I access them?

I want my columns to be:
1. Barcode
2. Category (hidden)
3. File_id
4. Name
5. VITAL
6. YAGUAR
7. MAXICONSUMO
8. DIARCO
9. MAKRO (not shown here since this particular product is not sold by Makro, but I will pad each product document with null values so they all have the same number of fields)
10. Masivos SA
11. Golomax (also not shown since this product is not sold by Golomax)

{
    "_id": "5b60c07849a13e8dcef0320f",
    "barcode": "4005808315093",
    "category": "Limpieza y Higiene",
    "category_id": 16,
    "file_id": "<img src='https://s3-sa-east-1.amazonaws.com/resources.centraldeofertas.com.ar/images/products/4005808315093.jpg' style='height:100px; width:100px'>",
    "name": "NIVEA Crema E/Seca 250cc",
    "providers": [{
        "provider": "VITAL",
        "cdo": false,
        "unitPriceWithTax": 64,
        "bulkPriceWithTax": 767.96,
        "unitsNeeded": 12
    }, {
        "provider": "YAGUAR",
        "cdo": false,
        "unitPriceWithTax": 59.28,
        "bulkPriceWithTax": 711.45,
        "unitsNeeded": 12
    }, {
        "provider": "MAXICONSUMO",
        "cdo": false,
        "unitPriceWithTax": 64.12,
        "bulkPriceWithTax": 769.44,
        "unitsNeeded": 12
    }, {
        "provider": "DIARCO",
        "cdo": false,
        "unitPriceWithTax": 55.69,
        "bulkPriceWithTax": 668.28,
        "unitsNeeded": 12
    }, {
        "provider": "Masivos SA",
        "cdo": true,
        "unitPriceWithTax": 63.98,
        "bulkPriceWithTax": null,
        "unitsNeeded": 6
    }]
}

Thank you so much!

janetcl

Okay, now I've formatted my data in the way that I want it for the table. However, I'm still getting the same error:
Uncaught TypeError: Cannot read property 'aDataSort' of undefined

The column titles array that I have looks like this:
["_id", product","category","Golomax","Masivos SA","MAXICONSUMO","MAKRO","VITAL","YAGUAR","DIARCO"]

Here is the new JSON object:

{
  "_id": "5b621ed5778a61d3a4840c53",
  "product": "<div class='text-wrap'>\" + <img src='https://s3-sa-east-1.amazonaws.com/resources.centraldeofertas.com.ar/images/products/1000007790375.jpg' style='height:100px; width:100px'> + ' <br>' + GUAYMALLEN Alfajor Oro 48gx24u + '<br>' + 1000007790375 + \"</div>",
  "category": "Golosinas",
  "Golomax": {
    "cdo": true,
    "unitPriceWithTax": 10.43,
    "bulkPriceWithTax": -1,
    "unitsNeeded": 24
  },
  "Masivos SA": {
    "cdo": true,
    "unitPriceWithTax": "N/A",
    "bulkPriceWithTax": "N/A",
    "unitsNeeded": "N/A"
  },
  "MAXICONSUMO": {
    "cdo": false,
    "unitPriceWithTax": "N/A",
    "bulkPriceWithTax": "N/A",
    "unitsNeeded": "N/A"
  },
  "VITAL": {
    "cdo": false,
    "unitPriceWithTax": "N/A",
    "bulkPriceWithTax": "N/A",
    "unitsNeeded": "N/A"
  },
  "YAGUAR": {
    "cdo": false,
    "unitPriceWithTax": "N/A",
    "bulkPriceWithTax": "N/A",
    "unitsNeeded": "N/A"
  },
  "DIARCO": {
    "cdo": false,
    "unitPriceWithTax": "N/A",
    "bulkPriceWithTax": "N/A",
    "unitsNeeded": "N/A"
  },
  "MAKRO": {
    "cdo": false,
    "unitPriceWithTax": "N/A",
    "bulkPriceWithTax": "N/A",
    "unitsNeeded": "N/A"
  }
}

Thank you so much! Please let me know if you need more information from me.

kthorngren

The column titles array that I have looks like this:
["_id", product","category","Golomax","Masivos SA","MAXICONSUMO","MAKRO","VITAL","YAGUAR","DIARCO"]

The columns option is looking for an array of objects. The above is an array of strings. If you are trying to use columns.title then you need an array of objects that have title as the key. Since you are using object based data you probably will also need to use columns.data. Basically your Javascript columns variable needs to be the same format you would use if you explicitly defined the columns in your Datatables init code.

Something like this:

[
  {data: '_id', title: '_id'},
  {data: 'product', title: 'product'},
.....
],

Kevin

janetcl

Thank you so much, Kevin! This helped a lot. Now I am trying to adjust the visibility of the columns through columnDefs and the "visibility" property. How can I reference the columns that I want to hide, given that their indices are not fixed? I was able to render a column in the way that I wanted using a render function, referencing the data using the row, but I am not sure if a render function can hide columns. I tried to reference the columns I wanted to hide below with targets: [1, 2, 3], but they are still visible. Let me know if you need more information.

$('#myTable').DataTable({
          "stateSave": true,
          "searchHighlight": true,
          "autoWidth": true,
          data: data,
          columns: columns,
          "columnDefs": [
          {
              // The `data` parameter refers to the data for the cell (defined by the
              // `data` option, which defaults to the column being worked with, in
              // this case `data: 0`.
              "render": function ( data, type, row ) {
                  return "<div class='text-wrap'>" + row.image + ' <br>' + data + '<br>' + row.barcode + "</div>";
              },
              "targets": 0
          },
          // hide the image and barcode columns
          { "visible": false,  "searchable": true, "targets": [ 1, 2, 3 ] },
          ]
        });

janetcl

Is this still beyond the scope of DataTables 1.10? At least, according to this post:
https://datatables.net/forums/discussion/46691/columndefs-targets-by-class-name-not-working-when-columns-defined-in-js

kthorngren

When you build your columns in Javascript do you know at that time if the column should be visible?

If yes, then you can apply the column visibility setting then.

Kevin

janetcl

At this point, I know what should be visible vs. hidden (in the future I will want to be able to show/hide columns dynamically on the click of an external button - I managed to accomplish this when the number of columns was static, so we will approach that problem when we get there). I tried to add the "visible" property to the columns[] array, and my array ends up like this:

However, the columns are STILL visible. What is going on here?

kthorngren

This example works:
http://live.datatables.net/quwucunu/1/edit

Not sure in your case. Can you provide a running example?

in the future I will want to be able to show/hide columns dynamically on the click of an external button

You would use the Column Visibility button. Here are some examples.

Kevin

janetcl

Hi Kevin,

My example is the exact same as the one you listed above, but the data is coming from a text file and then parsed into a JSON object. I screenshotted the columns[] array that is being processed by DataTables as so:

        $('#myTable').DataTable({
          "stateSave": true,
          "searchHighlight": true,
          "autoWidth": true,
          "responsive": true,
          data: data,
          columns: columns

As a result, I don't know what could be wrong besides the fact that my data is loaded in via ajax, and I am not sure how to attach a text file to a DataTables live.

I used Column Visibility in the past, but I want a way to reference the data within a row in ColumnDefs (I am already successfully doing this in a row callback). Is it possible to iterate through all of the fields in each data JSON object in a columnDefs render() function? Let me know if you need any clarification. Thanks!

janetcl

The issue was that stateSave was on. Is there a way for StateSave and dynamic column visibility to be compatible together?

janetcl

Actually, I realize that in the row callback I am having problems referencing a specific row. I tried iterating through every field in the data, using a variable i to keep track of the column number, but I realize that the order in which the iterator goes through the JSON object is not the same as the column ordering. This is what I am using:

$(`td:eq(${i})`, row).css("background-color","#ffbbff");

Is there a way to reference the cell based on its column title? Or the column className? This is both for within a row callback and in ColumnDefs. If not, I can try to use a HashTable with column titles as keys and column indices as values, but this is obviously not ideal. Sorry to bombard you with questions; thank you so much!