Extended data source options with DataTables

allan

Hello all,

Alongside the release of DataTables 1.8 beta 1, I've introduced a new section to the DataTables web-site - a blog :-). In the blog I'll give examples of how DataTables can be used in sites, and discuss various techniques for interacting with DataTables.

The first post is called "Extended data source options with DataTables": http://datatables.net/blog/Extended_data_source_options_with_DataTables

Rather than have comments at the bottom of the post, I plan to have post-blog discussion here in the forum. I'll create a new thread for each forum post for the discussion.

Any and all feedback on the first post and how I've pitched it is most welcome!

Enjoy,
Allan

jobkingori

First of all, thanks for taking the time to reply.

Two, apologies for the duplicate here http://datatables.net/forums/comments.php?DiscussionID=4878&page=1#Item_2 so i'll just move the discussion here so that other people can benefit.

Took your advice and checked out the new version 1.8-beta1 ... quite cool. Upgraded and everything worked out quite fine!

However, I added this to my js call to try take advantage of the new data handling feature... the table stopped working ...

[code]
"aoColumns": [
{ "mDataProp": "fullname" },
{ "mDataProp": "extended.0" },
{ "mDataProp": "user_group" },
{ "mDataProp": "gender" },
]
[/code]

My server side column specification in the php file is

[code]
$aColumns = array( 'fullname','extended','user_group','gender');
[/code]

Please note that the extended field holds a JSON array & without the "aoColumns" specification the table works however the extended field holds the entire JSON array hence why I was trying to fetch the first field using "extended.0"

Any ideas what I could be doing wrong. Have gone through the docs countless times :-( and even checked my syntax ...

An example of the data held in the extended field is

[code]
{
"check_in_date":"5",
"check_in_month":"04",
"check_in_year":"2011",
"institution":"Choice 2",
"degree":"Choice 2",
"field_of_study":
"Electornics and Computer Engineering",
"major":"Engineering",
"year_of_study":"Choice 2",
"gpa":"Choice 2",
"motivational":"Motivated",
"industry":"Choice 2",
"duration":"2","employment":"1",
"renumeration":"0",
}
[/code]

The only thing that I can come up with is that the resultant array returned by the server side script doesn't obey the array structure that datatables expects since it puts the array above in another array "aaData"

Just my two cents

jobkingori

Actually I think I figured out the problem though I'll need some clarification ...

My situation is more of an object of an object and so your deeply nested example is closest to my situation ... this is your example data from deep.txt (please note that the object in 'platform isn't in quotes')

[code]
{ "aaData": [
{
"engine": "Trident",
"browser": "Internet Explorer 4.0",
"platform": {
"inner": "Win 95+",
"details": [
"4",
"X"
]
}
}
] }
[/code]

In my case, I had a JSON string stored in the extended field ... wouldn't the result of the extended field be placed inside double quotes? " .. " Something like this ... (See the difference in the platform field which is now in quotes)

[code]
{ "aaData": [
{
"engine": "Trident",
"browser": "Internet Explorer 4.0",
"platform": "{
"inner": "Win 95+",
"details": [
"4",
"X"
]
}"
}
] }
[/code]

Could this be the reason, however small affect datatables and cause it not to understand the result of the server-side script?

allan

The last JSON code you have above is not valid JSON ( http://jsonlint.com is awesome for checking JSON) - is multiple line strings were allowed (which they aren't) then platform would just be a string in the example above, rather than a child-JSON object. However - I doubt it will be getting that far since strings can't be multi-line unless they are escaped (unless of course you've formatted the code above to make it easier to read - in which case the first point stands - it's a string).

Hope this helps!

Allan

jobkingori

Ok ... makes sense. After much checking I finally got it to work. I now have my data displayed even after adding the aoColumns property as shown below ...

[code]
"aoColumns": [
{ "mDataProp": "fullname" },
{ "mDataProp": "extended" },
{ "mDataProp": "user_group" },
{ "mDataProp": "gender" }
]
[/code]

and my server side column arrangement call is

[code]$aColumns = array('fullname','extended','user_group','gender');[/code]

This is the result of from the server side ... (just place the output of the server side on the page for debugging purposes)
[code]
{
"sEcho": 0,
"iTotalRecords": "5",
"iTotalDisplayRecords": "5",
"aaData": [
{
"fullname": "Job King'ori Maina",
"extended": "{\"check_in_date\":\"5\",\"check_in_month\":\"04\",\"check_in_year\":\"2011\",\"institution\":\"Choice 2\",\"degree\":\"Choice 2\",\"field_of_study\":\"Electornics and Computer Engineering\",\"major\":\"Engineering\",\"year_of_study\":\"Choice 2\",\"gpa\":\"Choice 2\",\"motivational\":\"Motivated\",\"industry\":\"Choice 2\",\"duration\":\"2\",\"employment\":\"1\",\"renumeration\":\"0\",\"login-updprof-btn\":\"Update Profile\"}",
"user_group": "3",
"gender": "1"
}
]
}
[/code]

The JSON above is valid JSON but where did all the '\' come from? Could the server-side script be adding these modifications and could this affect when I try access nested fields e.g. { "mDataProp": "extended.institution" } When I check the database directly this is what I get in the extended field ... so my date is on

[code]
{
"check_in_date": "5",
"check_in_month": "04",
"check_in_year": "2011",
"institution": "Choice 2",
"degree": "Choice 2",
"field_of_study": "Electornics and Computer Engineering",
"major": "Engineering",
"year_of_study": "Choice 2",
"gpa": "Choice 2",
"motivational": "Motivated",
"industry": "Choice 2",
"duration": "2",
"employment": "1",
"renumeration": "0",
"login-updprof-btn": "Update Profile"
}
[/code]

allan

Whatever is encoding your JSON is treating whatever you have assigned to the 'extended' property as a string - and is therefore encoding it as such. How it should be done will depend upon what server-side script language you are using and what JSON encoder you are using.

Allan

jobkingori

I'm using PHP on the server side .... specifically the ojects.php file that's in the 1.8 examples zip file ... so I didn't create my own server side script. I just updated it with my details etc ...


In the docs, the server side php script is already designed to return an appropriate JSON result with correct encoding right? Ok, that may be just an assumption, just thought if its in the examples then it abides by the rules. So ... does this mean that due to my unique extended field data I need to make modifications to adjust to my situation and treat that field as having JSON?


I must say thank you Allan. Really thanks. Its been a steep learning curve throughout the week but I believe I'm at the last step! :-)

allan

So with the extended property - are you just assigning a value which has been ready from the database (i.e. a string which has not be converted into PHP array notation)? That would be my guess :-) You would need to evaluate the string as a JSON object and then assign it to the output array.

Allan

jobkingori

Yes, if I understand you correctly ... that's exactly what I've done. See ... in the extended property I have valid json so when I call it I guess it fetches it as a string instead of a json object or array ...

Makes sense why the entire json string is placed in quotes ... also thought it was weird that jsonlint wouldn't auto indent the json extended field data when I was validating it ...

Anyway, I was perusing all the server processing PHP scripts in your examples and I realised something. its very easy to assume that the scripts are identical but in fact they aren't in that, the way they process the output array is a little different. This might have been obvious to you since its your code :-) but its made me realise why I've been getting unexpected results.

Now with a little more direction :-) Lemme read up a little bit and see how I can format that output array ...

Funny how what you've been trying to explain all this time now just comes together! :-)

allan

Yeah - I need to do something about those server-side scripts... they are all slightly different, but I wasn't sure of the best way of doing it. Might make a class of it and allow it to be extended for the various options.

Btw - you should be able to just do something like $myArray[] = json_decode( $myJsonString );

Allan

jobkingori

Allan ... you my friend are the man! IT WORKED!!!

I think you have a done a great job even with the server-side scripts. The examples really nail it even further its just that, in my case I thought the script handled all situations and my situation was a bit different. Maybe you could add this scenario to the server-side processing examples so that other people can benefit. Plus the ajax examples and the different scenarios i.e object with subarray, array with subobject, deeply nested should serve as a good indicator to show that the output format of the source is really important.

It might be difficult to come up with a script that covers all possible scenarios so also a blog post about modifying the server-side script to cater for individual formatting needs might come in handy :-)

So for anyone else out there who might have had the same situation as I did, here are my modifications to the server side script.

[code]
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i

pimperish

Allan, this is great! I started using dataTables back in version 1.4 or so. At the time, I jumped right in and heavily customized it, adding a number of features I wanted, and stripping out some things to trim down as much as possible for my requirements. One of the big things(for me) I added was support for addressing columns by name. I added a bit of code that just took the sName and stored the column index it was associated with in a hash. Then whenever a column reference was required, it checked to see if it was an number, if not, it looked up the index.

Hmmm...now that I take a quick look around the docs again, I'm not sure the new feature goes that far. I would like to be able to use the mDataProp defined for a column as the parameter to, for instance, fnFilter. If that's not currently possible, I would add that as a feature request :)[update: I see now that it doesn't do this]

Over time, you've gradually added a bunch of the mods I had (sorry, never really had the time to test properly and share back) which is awesome because I won't have to merge in updates anymore...well, after just one more thing gets added: metadata plugin support(but I'll bring this up in another post)

Anyway, the blog post does a great job of presenting the feature and what one might do with it.

arnigunnar

This is sooooo awesome!

I am coming from a .NET background and this just makes my life much, much easier. Now I can just list out my classes from the javascript webservice, and the column properties will take care of the rest. So awesome.

Thanks!

allan

@pimperish, @arnigunnar: Thanks very much for the feedback :-). Excellent to know that the new data source options hit the mark!

@pimperish: Column references are still done on an index basis through the API, rather than by name. However I would imagine that it would be quite trivial to have a plug-in API function to take a name and match it up to an index given by sName - then fnFilter could be done by something like fnFilter( 'allan', 'col1' ); - is that the kind of thing you mean? It's a nice idea that certainly.

Regards,
Allan

gormanst

In the below JSON I am able to reference requestStatus.status but not the second line overview.feedOverview.status. Could you tell a newbie what I am doing wrong? Thx. in advance.
"aoColumns": [
{"sTitle": "Status", "mDataProp": "requestStatus.status" },
{"sTitle": "Mission", "mDataProp": "overview.feedOverview.status" } ]
JSON
{
"aaData": [
{
"requestStatus": {
"status": "OPERATION_COMPLETE",
"errorText": null
},
"overview": {
"feedOverview": [
{
"status": "RUNNING",
"recordingStatus": "STOPPED",
"picteId": "1323369508",
"feedName": "Predator",
"missionName": "Mission",
"startTime": "2011-12-08T12:00:00",
"stopTime": "2011-12-08T16:00:00"
}
]
}
}
]
}

gormanst

Forgot to tag the code as code
[code]
$('#example').dataTable( {
"bFilter": false,
"bPaginate": false,
"bInfo": false,
"bJQueryUI": true,
"bSort": false,
"bProcessing": false,
"sAjaxSource": "url",
"aoColumns": [
{"sTitle": "Status", "mDataProp": "requestStatus.status" },
{"sTitle": "Mission", "mDataProp": "overview.feedOverview.status" } ] });
[/code]

jwilms

Can I use this named fields also in the fnRender function ? Or is there any way to work with tose fields without knowing the index of the field in oObj.aData[x] ?

allan

feedOverview is an array - you would need:

[code]
overview.feedOverview.0.status
[/code]

for your mDataProp there.

Allan

pendensproditor

This feature is enormously helpful. I'm now stripping out huge amounts of code in my app, the sole purpose of which was to make it less painful to deal with rows as arrays.

However I'm trying to figure out how to use this gracefully with fnRender. I've adopted this generic approach:

[code]
"aoColumns": [
{
"mDataProp": "someKey",
"fnRender": function (o) {
var v = o.aData[this.mDataProp];
// return 'v' with formatting
}
}
]
[/code]

Maybe the value of the current column could be made available automatically? For example:

[code]
"fnRender": function (o, v) {
// return 'v' with formatting
}
[/code]

Developers could still access any other column the old way.

allan

This is an excellent idea. I think I should add two things - 1. the value as a second parameter and 2. mDataProp as a property of the first parameter.

It will probably be the start of next week before I can do that, but I'll get it into DataTables 1.9.

Regards,
Allan

allan

I've just committed this change and its now available in the 1.9.dev.2 nightly build on the downloads page: http://datatables.net/download :-).

Regards,
Allan

pendensproditor

Allan, this is kind of in the same vein, so I wasn't sure whether I should start a new thread. I'm not entirely familiar with 1.9 so this may already be implemented.

It seems to me that column sorting is the next thing that needs to be less dependent on array indexes. In our current app it's causing us some trouble. When we sort on column 3, for example, the datatable service is called with iSortCol_0 set to 3. If we add a new column to the left later on, that same column is now column 4.

This requires us to maintain an array of field names in our service, and if the above happens we need to update the array to match the integer the datatable is sending. If we have two different datatables using the same service but displaying different data, we have to maintain an array for each. It's a lot of logic that seems unnecessary -- the service shouldn't have to worry about any of this. Things get even weirder when we need to create fake columns that aren't in the original data.

The solution to everything is to set iSortCol_0 to a field name (mDataProp) instead of an integer. No more updates to the service and no more maintaining arrays.

Does the datatables framework already support this kind of sorting? If not, would you be open to implementing it?

allan

Hi,

Yes DataTables provides the information to solve this particular problem already :-). The key thing to remember here is that I've tried to strike a balance between ease of use for initial setup (the most common usage is index based) and flexibility (mDataProp). I don't want developers to need to specify mDataProp for every single table - obviously there are cases where it will be needed for all tables on a site, but it isn't needed for reading from a DOM source for example.

DataTables sets the mDataProp_{int} for each column. So if you get the first sorting column as column index 3, you would look up what the mDataProp for that is using mDataProp_3. If you have a look at this script, you'll see that I've got this conversion in there: https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/objects.php .

Allan

pendensproditor

I don't know how I missed those mDataProp_# values in the request params. Perfect, thank you very much.

armani

Hi Allan,

Can you please help me to see how do I get nested array data, if number of items in the sub array is dynamic, see below for my data set (in details )? So I can't just hard code details.0.name, because number of items in details array is dynamic.And I am doing server side fetch.

{ "aaData": [
{
"engine": "Trident",
"browser": "Internet Explorer 4.0",
"platform": {
"inner": "Win 95+",
"details": [
{"name":'a',"class"='1'},{"name":'b',"class"='2'},{"name":'c',"class"='3'}
]
}
}
] }

allan

What I would suggest you do is to use mDataProp as a function ( http://datatables.net/blog/Orthogonal_data ) - then you can simply do something like return data.details.join(' ');

Regards,
Allan