DataTables 1.5 beta (server-side processing and more)

allan

Hello all,

I'm very pleased to release a beta version of DataTables 1.5. After some intensive development this week, there are a couple of very significant new features including:

- Support for server-side processing of data
- Ability to get/display data from any source (Gears for example)
- Adobe AIR support
- Ability to show or hide columns programatically through the API.

This is very much a beta release, but it seems to be hold up quite well in my tests, and it also fixes a number of issues that were present in 1.4. I've also build a unit testing framework for DataTables, which will thoroughly test all releases in future, but I'm still in the middle of writing the tests. 1.5 won't be released until I'm happy that I've got a comprehensive test suite (we all want solid software!), but hopefully that won't be too long.

Download: http://www.datatables.net/releases/dataTables-1.5.beta.zip
Examples: http://www.datatables.net/1.5-beta/example_zero_config.html

So lets get the skinny on these new features.


- Support for server-side processing of data

DataTables has a new feature parameter called "bServerSide", if this is set to true then all processing is done on the server-side rather than the client-side, using the "sAjaxSource" target. Example: http://www.datatables.net/1.5-beta/example_server_side.html .

What this means is that DataTables is now ideal for displaying huge amounts of data. DataTables will handle all of the events on the client-side, and send a request to the server for each draw it needs to make. The data fired back from the server will then be displayed. All the features of DataTables you know and love are supported, multi-column sorting, filtering, pagination etc. Obviously it's up to you to do the processing on the server-side and with your database interaction, but DataTables tells you what it needs through the following "get" variables:

iDisplayStart - Display start point
iDisplayLength - Number of records to display
sSearch - Global search field
bEscapeRegex - Global search is regex or not (you probably don't want to enable this on the client-side!)
iColumns - Number of columns being displayed (useful for getting individual column search info)
sSearch_(int) - Individual column filter
bEscapeRegex_(int) - Individual column filter is regex or not
iSortingCols - Number of columns to sort on
iSortCol_(int) - Column being sorted on (you will need to decode this number for your database)
iSortDir_(int) - Direction to be sorted
sEcho - Information for DataTables to use for rendering

DataTables expects to receive a JSON object with this following properties:

iTotalRecords - Total records, after filtering (not just the records on this page, all of them)
iTotalDisplayRecords - Total records, before filtering
aaData - The data in a 2D array
sEcho - An unaltered copy of sEcho sent from the client side



- Ability to get/display data from any source (Gears for example)

Building on the server-side processing, DataTables allows you to override it's standard get function ($.getJSON) by using the "fnServerData()" init parameter. This function expects exactly the same input as $.getJSON, and must call the callback function (the third parameter) with the JSON object to be displayed (as described above) - see http://docs.jquery.com/Ajax/jQuery.getJSON .

In this manner, you can get the data from any source you want (a Gears or AIR database for example!). With this release, DataTables supports four methods for gathering data, DOM, Javascript array, JSON source and server-side processing. The fnServerData() function, means that new data sources are just a function away.


- Adobe AIR support

For the most part DataTables runs great in AIR. However, one issue is that the multi-column sorting feature using 'eval()' - which AIR rejects on security grounds (which is completely fair). This release allows single column sorting in AIR (multi-column sorting is ignored in AIR).


- Ability to show or hide columns programatically through the API.

The new API function "fnSetColumnVis()" allows you to enable or disable the visibility of a particular column on the fly. It expects two parameters:

1. (int) the column to show/hide
2. (bool) visible or not

The internal changes required for this make it possible in future to add re-ordering of columns etc, which is quite an exciting feature, and I hope to make this available as a plug-in at some point (as time allows).

Hope you all enjoy working with this new release, and looking forward to 1.5 final.

Allan

artlung

I'm very hopeful about the server-side handling of data. I was running into a problem refreshing the view of a large set of data (>1600 rows with 8 columns of data) and sometimes the browser hangs. Looking forward to trying this out today and tomorrow.

I really like dataTables very much. Very useful software. I answered a question about initialization of dataTables over on StackOverflow.com today. :-)

allan

A minor update: http://www.datatables.net/releases/dataTables-1.5.beta.2.zip

Two small changes:
- Multi-column sorting is now fully supported in Adobe AIR :-)
- The standard sort is a touch faster


@artlung: Yup, hopefully this new version will allow for lager data sets to be dealt with quite easily. Surprised that is struggles at 1600 records though - it should really cope with much more than that on the client-side. 1.5 actually includes a number of improvements in terms of processing speed, perhaps that will help...

Regards,
Allan

artlung

Sorry, I did not provide enough information. I had a version that was initialized with table data, but I switched to Ajax, and was doing refreshes to the data when there were updates to the underlying data, but could not get single row updates working. I was refreshing the whole set of data over Ajax, which was what was slow.

Got a basic version working last night, will continue to flesh it out today. I need to get the sorting working appropriately today.

In general, excellent timing on this release!

allan

@artlung: Ouch - yes I can see that reloading 1600 rows frequently would bring it to it's knees! Hopefully the server-side processing will go well to address that. :-). Let us know how you get on, if you would, with bug reports and enhancement ideas...

Allan

jwhyne

The beta looks awesome. Can't wait to play around with it some more.

fnSetColumnVis dies if my table does not have a tfoot defined when trying to add or remove children to nTrFoot.
I added "if( nTrFoot )" checks at the beginning of lines 1325, 1347 and 1362 and everything works perfectly.

Keep up the great work. Can't wait to see the final!

allan

Hi jwhyne,

Great stuff - thanks for spotting that. I've just put your suggested fix into place and I'll release that in the next beta (sometime later on today). :-)

Allan

zygimantas

Is iDisplayLength settings parameter broken in 1.5b1 and 1.5b2? iDisplayLength is ignored and 10 rows are displayed.

allan

Hi zygimantas,

Yes indeed you are correct. I broke it with a change I made in updating that code for 1.5. I've just released 1.5 beta 3 with a fix: http://www.datatables.net/releases/dataTables-1.5.beta.3.zip

This is why it's important for me to finish the unit testing :-)

Allan

rogerc

Hi allan, and congratulations for the nice work.

I've been testing the server-side processing with a 12500 rows and works really fast (cool!).

However, I have a problem: some columns get sorted as expected while others don't get sorted. I've checked the php part and the JSON is sent ordered as expected, but it seems the data in the table won't refresh as it should (maybe it's not a refresh problem, but the data doesn't move).

I can't send you my database as it contains some private data, but I'll try to reproduce the problem in another testing database and send it to you in case you need it.

Anyway thanks for your time.

rogerc

allan

Hi rogerc,

Great to hear it works with a large data set!

It is odd that sorting doesn't work quite as expected. The sorting on my own server-side example appears to work correctly, and I would have thought it would scale up very well on the back-end. If you could put together a little demo showing the problem, that would really be most useful!

Thanks
Allan

rogerc

Hi again allan,

I don't have anywhere to hang a dummy table. However I've tried with some other data and you can reproduce my problem very easily creating any long dummy table; I used http://www.generatedata.com/#generator which is very handy for that purpose.

If you want my dummy table (and the code to reproduce the problem), please contact me and I'll send it to you by mail.

Thanks a lot,

roger

allan

Hi Roger,

Thanks for the link to generate data - I didn't know about that service - very useful indeed! However, I've created a table with random information and it's still sorting as expected. If you could send me a bit more information that would be very useful. http://www.datatables.net/contact - I'll ping you back from there if you drop me a message :-)

Regards,
Allan

rogerc

Hi again,

finally I solved my problem. There were so many rows that came from another database, and I didn't see there were some single quotes in there so the JSON wasn't OK at all.

Sorry for the inconvenience and thanks for the good job,

roger

allan

I've always wondered why jQuery doesn't throw an error when it encounters a json string that it can't parse. I think it might be that I need to register a 'failure' function to the json get. I'll certainly have a look at this for the next release as when debugging this it can take ages to realise what is going on!

Thanks for the update!
Allan

doc

Hey Allan
does this address the issue I had a week ago about sorting on release numbers such as 1.2.3 and 1.2.4?

Regards
Dave

allan

Hi Dave,

Yes it should do. It should now sort those "numbers" as strings... If it doesn't - it's a bug!

Allan

doc

Hey Allan
That's great,
thanks as always :)

Dave

tdktank59

Hey Allan,

So anyways the framework im using does not allow query strings.

So I was wondering where the query string is being created.
The closest I found to it in the script is line 2701.

I need to change it into / and no ? or = and what not

key/value sort of deal. (Don't worry my script handles it)

for example http://your_site.com/controller/method/key/value/key/value/...
Not asking for you to change your script to handle this just wondering where I would start to modify your script to support my needs.

tdktank59

Another question I have is sorting hidden columns.

I have a need to sort through some hidden columns but that data is irrelevant besides the initial sort.

tdktank59

All right found all the madness!

Here's where they are at and how to make it work for my needs... Would be nice to change these but I doubt jquery will do that...

there are 3 lines in the jquery script (un minified version)

change: (in the file jquery.1.3.2.js)
line: 3458 (or so)
s.url += (s.url.match(/\?/) ? "&" : "?") + s.data;
to
s.url += (s.url.match(/\?/) ? "/" : "/") + s.data;

line 3737 (or so)
s[ s.length ] = encodeURIComponent(key) + '=' + encodeURIComponent(value);
to
s[ s.length ] = encodeURIComponent(key) + '/' + encodeURIComponent(value);

line: 3762 (or so)
return s.join("&").replace(/%20/g, "+");
to
return s.join("/").replace(/%20/g, "/");

which made it a valid query string for what I needed in the format

www.myurl.com/controller/model/key/value/key/value...

allan

Hi tdktank59,

Thanks for posting your solution to the problem that you were facing. Can I just check my understanding - was it that the url was not correctly encoded for the get request? If that is the case, then perhaps the easiest way of doing what you are looking for is to use a custom json get function for the server-pull of data: http://datatables.net/1.5-beta/examples/server_side/custom_vars.html

What you could do there is loop over the data array and encodeURIComponent() the variables. That way you avoid modifying jQuery core - which might cause inter-op issues for you in future when you upgrade the framework.

Regards,
Allan

tdktank59

Yeah bassically,

The way its working right now it goes like this:
www.your_url.com/page?key=value&key=value

when i need it to go:
www.your_url.com/page/key/value/key/value

My framework (codeigniter) disallows query strings by default and I want to keep it that way. So i was trying to modify it to use / instead of ?,& and =

Im not quite sure how to use the encodeURIComponenet() works can you point me to some documentation on this?

allan

Ah ha - I see what you need, thanks for posting an example. Yes, this is a tricky one and in fact encodeURIComponenet() won't really help here (it's just an escape function for characters which have meaning in a URI).

I think you are right, the only what to get the key/value pair to be like this when posted from jQuery is to modify jQuery itself. It might be worth bringing it up on the jQuery mailing list - but I would imagine that they will point to the framework being non-standard in how it accepts POST information. As such, if your modified jQuery works for you - go with that :-)

Allan

tdktank59

well it handles POST fine... just not GET... it dosnt like the query strings... Post is fine since its not being sent in the url.

The whole point of the / instead of ?&= is to make it more Search engine optimized. Many frameworks do this.
Not just this one. So saying its non-standard in the get aspect is not 100% true. To many Frame works this is how it is (at least the ones I have used.)

Not trying to say your wrong or be rude.

Anyways as long as I dont need jquery connecting offsite then it should be fine. (well as long as it dosnt need ?&= lol

allan

Sorry yes - I meant non-standard in the sense that this is not how HTTP expects variables to be passed around. However, I certainly see the benefit of doing it this way.

if you are using server-side processing then you can use POST to pass the values to the server-side if you wish: http://datatables.net/1.5-beta/examples/server_side/post.html

Allan

tdktank59

Hey got that working thanks!

However found an odd bug... Not sure exactly however.

as soon as I add "bServerSide": true, it breaks and shows all records.
However when I remove it, it works...

tdktank59

Is there a way to have a dropdown (seperate of datatables) for example

http://img21.imageshack.us/img21/4039/exampleovs.png

I can pull these values in when I do other requests but I was wondering if there was a way to call the json request when ever?

allan

The bServerSide parameter expects that there will be a degree of server-side processing going on for each page display made by DataTables. If this logic isn't present (such as doing the filtering, limiting the return number of rows) then it's undefined behaviour for what will happen. Server-side processing is ideal for if you have large sets of data - anything over 1000 rows (or possibly even 500 depending on the complexity).

Regarding your dropdown question - I'm not 100% sure what you are looking for - is it that the table information should be based on what is contained in the drop downs? What you can do is make use of the fnReloadAjax() plug-in API function ( http://datatables.net/plug-ins#api_fnReloadAjax ) - with this you can completely reload the table based on whatever data is selected at the time (just add the variables as GET request variables).

Hope this helps,
Allan

tdktank59

Perfect! Exactly what I was looking for!

Sorry for asking so many questions im slowly learning Jquery as well as datatables.

I foresee a few beers in the future for you! (For a great product and the help!)

allan

Excellent - good to hear that does the job!

Questions are good (as is beer - thanks) - it's a learning experience for all of us :-)

Allan