render plug-in column-type detected

didiergm

Along the same vein as the sorting plugin, is there a way to have a render plugin ?
I find myself in a catch-22 situation: my DataTables are created and populated via ajax calls, first the column definition then in a subsequent ajax call the data. . I need the dates to be formatted in a way the user understands (typically dd/mm/yyyy) but here is the catch
1- I can't use the fnRender function in the column def as I do not seem to be able to send via ajax as part of the aoColumndefs a fnRender function which is recognised by js
2- I can't format the dates in the SQL query because then the sql sorting on that column is wrong (or not what a date sorting should be)

So currently I am thinking, that unless I can find a way to get 1 to work, a rendering function based on column type detection would solve my issue, but my understanding of the inner workings of DT is far too light to even start something like this. Any pointer or direction would be gratefully received.

didiergm

In the interim I found a way to implement a type detection mechanism on the server side which does what I need w/o impairing the sort.

allan

Hi didiergm,

An interesting topic - thanks for your question!

> 1- I can't use the fnRender function in the column def as I do not seem to be able to send via ajax as part of the aoColumndefs a fnRender function which is recognised by js

This is a security feature of how jQuery loads JSON data sources. A function is valid Javascript, but not valid JSON - therefore, the JSON parser rejects the function (this is in order to prevent XSS attacks). One possible option is to load the target as a Javascript source file, rather like JSONP does to overcome cross domain security measures, but that sounds rather cumbersome to me.

> 2- I can't format the dates in the SQL query because then the sql sorting on that column is wrong (or not what a date sorting should be)

I'm surprised by this - if your SQL looks something like this:

[code]
SELECT DATE_FORMAT(stamp, '%W %M %Y') as stampformatted
FROM mytable
ORDER BY stamp ASC;
[/code]

It should order by the original field.

An alternative option to having the SQL engine doing the formatting would be to have your server-side scripting environment (PHP, C# or anything else) do the formatting for you. You just need to loop over the array and do the formatting required.

Having said that, it certainly should be possible to do what you are looking for in DataTables / Javascript - although how exactly you want to do it depends on the exact setup that you are using. There are a number of options off the top of my head:

1. With aoColumnDefs you could use its ability to target columns of a given class (i.e. assign the class "time" to the TH elements you know are going to be used for columns of dates) and have the fnRender function already defined for that. This won't work with dynamically loaded JSON as noted before, but if you need to load the aoColumnDefs dynamically you could just push on another client-side definition when it has been loaded.

2. You could intercept the data being returned from the server using fnServerData and format it as required using a simple loop before passing it on to DataTables to be drawn on the page.

I've assumed here that you are using server-side processing - is that correct, or is it Ajax sourced data with client-side processing? The exact answer that you will need will depend quite heavily on that!

Regards,
Allan

didiergm

Thanks Allan for you detailed response.
Yes - I am using server side processing
-Of course the SQL you present here would work, but I am using a home-grown php developement environment which had never to deal with this kind of issue before so is not prepared to sort by a column not in the result set, so it would sort by 'stampformatted' instead of stamp, the original field.
Both answer 1 or 2 would work I believe; I would have to delve into the fnRender to see how I could find the class of the current TH and then define a generic fnRender. this is what I believe would work best.

allan

Do do the method with a class in aoColumnDefs, this is the basic idea for what you need:

[code]
$('#example').dataTable( {
"aoColumnDefs": [
{
"fnRender": function (o) {
return "formatted";
},
"aTargets": [ "stamp" ]
}
]
} );
[/code]

That would then apply the renderer to any column where the TH element has a class of "stamp". You could add that class based on information returned from the server if you want the server to define the table completely, or already have it available in the HTML used for the table.

To add this to an aoColumnDefs object returned from the server, you just need to do something like this in your Ajax callback where the table is initialised:

[code]
cols.push( {
"fnRender": function (o) {
return "formatted";
},
"aTargets": [ "stamp" ]
} );

$('#example').dataTable( {
"aoColumnDefs": cols
} );
[/code]

Regards,
Allan