Formating external data source for additional information (server side solution)

giRobert

Hello guys,

I'm using datatables for very large tables.
So currently I am rebuliding them from my old structure to the new server side structure: https://datatables.net/examples/data_sources/server_side

One example:

The question now is, how can hand over the yellow marked data? im very desperate...

colin

This thread should help, it's asking the same thing.

Cheers,

Colin

giRobert

Hey Collin,

thanks for your reply.

The thread you mention is 6 years old and to be honest it is impossible for me to make this work via individual js functions.

i must influence the tr and td based on much business logic which i can only use when creating the data array structure. not afterwards.

there must be a better solution. one suggestion could be to make the data array look like this:

What do you think? Is there anything out there in datatable? Or can i achieve this somehow?

kthorngren

That data structure is technically correct. You are placing objecting is array notation, for example you should have this:

{ "tr": {"classes": "unfinished"} }
....

Even if what you depicted had proper array and object syntax Datatables wouldn't easily support that structure. I built a small example based on one of your rows:
http://live.datatables.net/jiteceju/1/edit

It uses Orthogonal data by using columns.render. It uses createdRow to apply the classes. note also that it has to reference each td by its array position. The same code will work for ajax loaded data.

Datatables only supports the HTML5 data attributes for DOM sourced tables. This way it can read in the attributes during Datatable initialization. Datatbles won't use them for ajax or javascript sourced data.

More information on supported data sources and structures can be found here and in the ajax docs.

Kevin

giRobert

Hey Kevin,

thanks a lot for your effort! This looks very good for my case. i think i could even make it dynamic so it would work for my flexible case.

the problem now is, i can not combine it with my ajax way.. i read the pages you refered to and did a lot of google-ing but i dont know how to refer to the retreived data.

in your case i am always getting "data.td undefined". i tried to set data but i cant make it.. i cant set the data like you did in the example.. it must be retreived via ajax..

kthorngren

Without seeing the data structure in the ajax response and your datatables config its hard to say. Can you post your Datatables init code and a snippet of the ajax response using the browser's network inspector?

You can debug the columns.render and createdRow functions to see what data you have. You can use the browser's debugger or console.log statements to see the values of data in the function.

Kevin

giRobert

Hey Kevin,

this is my init:

$("#ajax_table123").DataTable( ajax_datatable_params );

these are my parameters (i used your code and dynamized it).

var column_string  = "[";

//this creates the logic for data_sort, data_search and sets the td value per column
for (var i = 0; i <= 4; i++) {
column_string  = column_string  + "{ data: null, render: function (data, type, row) { if (type === 'display') { return data.td[" + i + "].content; } if (type === 'sort') { return data.td[ " + i + "]['data-sort']; } if (type === 'filter') { return data.td[" + i + "]['data-search']; } return data.td[" + i + "].content; } },";}

column_string  = column_string  + "]";

var ajax_datatable_params = {
    "pageLength": 20,
    "lengthChange": false
    "order": [[ 0, "asc" ]],
    "preDrawCallback": function (settings) {
        //hiding pagination if there is only one page
        var api = new $.fn.dataTable.Api(settings);
        var pagination = $(this)
            .closest('.dataTables_wrapper')
            .find('.dataTables_paginate');
        pagination.toggle(api.page.info().pages > 1);
    },
    "processing": true,
    "serverSide": true,
    "ajax": {
        url: "URL123"
    },
    "columns": eval(column_string),
    "createdRow": function (row, data) {
        //adds the TR class
        $(row).addClass(data.tr.classes);
        //adds the TD class
        $(row).find("td").each(function( id ){
              $(this).addClass(data.td[id].classes);
         });
      }
};

this is the response I get from the Ajax Request:

{"recordsTotal":205,"recordsFiltered":205,"draw":0,"data":"[ { tr: { classes: 'unfinished' }, td: [ { 'classes': 'classYellow', 'data-search': 'Berlin', 'data-sort': 'DEU', 'content': 'Berlin' }, { 'classes': 'center', 'data-search': 'blubb', 'data-sort': '1349220199', 'content': '1949' }, { 'classes': 'classYellow', 'data-search': 'Berlin', 'data-sort': 'DEU', 'content': 'Berlin' }, { 'classes': 'center', 'data-search': 'blubb', 'data-sort': '1349220199', 'content': '1949' }, { 'classes': 'center', 'data-search': 'blubb', 'data-sort': '1349220199', 'content': '1949' } ] }, { tr: { classes: 'finsihed' }, td: [ { 'classes': 'classBlue', 'data-search': 'Berlin', 'data-sort': 'PAL', 'content': 'Palermo' }, { 'classes': 'left', 'data-search': '1949', 'data-sort': '1349220199', 'content': '1723' }, { 'classes': 'classYellow', 'data-search': 'Berlin', 'data-sort': 'DEU', 'content': 'Berlin' }, { 'classes': 'center', 'data-search': 'blubb', 'data-sort': '1349220199', 'content': '1949' }, { 'classes': 'center', 'data-search': 'blubb', 'data-sort': '1349220199', 'content': '1949' } ] } ]"}

The error I get is

Uncaught TypeError: data.td is undefined

I am pretty sure that i need to tell my datatable that "data" contains the ajax data somehow....

kthorngren

Your response doesn't look like valid JSON format. Take a look at this example. Click on the Ajax tab or use the browser's network inspector to see the XHR response, for example:

{
    "draw": 1,
    "recordsTotal": 57,
    "recordsFiltered": 57,
    "data": [{
        "first_name": "Airi",
        "last_name": "Satou",
        "position": "Accountant",
        "office": "Tokyo",
        "start_date": "28th Nov 08",
        "salary": "$162,700"
    }, 
    ............
    ]
}

Notice everything is in double quotes but the data array is not in quotes.Your data array is a string (in quotes) and you have single quotes throughout. You can use jsonlint.com to validate your JSON response.

You have enable server side processing ("serverSide": true,). Are you going to have many thousands of rows? You might not need server side processing and if not you can remove this option. You can still use ajax to fetch the data.

It doesn't look like your server script is following the server side processing protocol. The draw parameter should never be 0. Its a sequence number and starts with 1 on Datatable initialization.

To start with you need to make sure to have a properly formatted JSON response. And if you need server side processing you server script needs to follow the protocol linked above.

Kevin

giRobert

Hello Kevin,

indeed, it was the data structures fault.
i fixed this and now i can talk to "data".
jeez... thanks a lot!