DataTables logo DataTables

DataTables 1.5 beta (server-side processing and more)
  • 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
  • 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. :-)
  • 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
  • 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!
  • @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
  • 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!
  • 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
  • Is iDisplayLength settings parameter broken in 1.5b1 and 1.5b2? iDisplayLength is ignored and 10 rows are displayed.
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Hi Dave,

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

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

    Dave
  • 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.
  • 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.
  • 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...
  • 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
  • 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?
  • 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
  • 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
  • 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
  • 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...
  • 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?
  • 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
  • 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!)
  • 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
  • Alright finally got around to trying to implement this fnReloadAjax() plugin.

    Heres the code I used

    http://tkensiski.pastebin.com/m60c101e1

    *There is the table and stuff as well, this is just the javascript parts that control the table.

    However When I click the dropdown which works.

    I reloads twice, the first time with all the parameters, second time without...
    the first one is page load, the second and thrid are done when I change the dropdown.

    http://img156.imageshack.us/img156/1328/31363843.png
  • Ah - you are using server-side processing as well as fnReloadAjax - that's what will be causing the double Ajax get. You don't want to use fnReloadAjax if you are using server-side processing because with the latter a call is made to the server on each draw anyway.

    What you can do here is pass custom variables (or manipulate the variables that DataTables sends to the server) using something like that shown in this example:
    http://datatables.net/1.5-beta/examples/server_side/custom_vars.html

    You can also modify the sSourceAjax parameter from the settings object (fnSettings()) if you want to change the get address. Then calling fnDraw() will update the table.

    Allan
  • Thats about how far I got...

    However I need to when the dropdown changes to refresh the data. (AKA get the info from the database)
  • Okay so just attach a 'change' event handler to the dropdown menu:

    $('#whatever select').change( function() { oTable.fnDraw(); } );

    If you want to do more clever stuff such as changing the source URL you can do so here.

    Allan
  • Nah just need to get the values from the dropdowns which is easy... Just need to send the ajax request again to get the new data based on the dropdowns and everything
  • I'm confused :-). Have you managed to do what you are looking for or not? It sounds like you have. If so - fantastic!

    Allan
  • No, Not exactly...

    I am able to get the values from the dropdowns...

    However I have yet to figure out how to make it reload the data when i select a value from the dropdowns.

    (this is separate from the tables filters)
  • So do you want to change the data source (the url) when a value in the drop down is selected?

    That could be done using the 'change' event handler and something like:

    oTable.fnSettings().sAjaxSource = "whatever";
    oTable.fnDraw();

    Allan
  • I am new jquery. I have to use the datatables in my project and access the data entered to the table in server side to update the database on submit. Can you please suggest me how can i do this?
  • Most of us use the editable plugin to modify data and send back to the server side:

    http://code.google.com/p/jquery-datatables-editable/

    jeditable is a separate script written on jquery. data tables utilizes it to set up editable cells and to send data back to the server side.

    this demo lets you see how editing a cell looks and feels:
    http://jquery-datatables-editable.googlecode.com/svn/trunk/inline-edit.html

    There's an example page somewhere (I can't find it) where someone opens the entire row for editing.
  • Hi

    I'm new to this Data table. I have datatable with a another submit button on my page.
    I'm developing dattable with server side processing. I'm able to edit cell and sorting filtering on server side.

    but when button click i want to pass 2 parameter to the server.
    how can i achieve this..
    here is my code sample which i used:
    var oTable = $('#tblAdjustmentErrorFile').dataTable({

    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "AdjustmentErrorFileHandler",
    "aoColumns": [
    {
    "bVisible": false
    }, //ID is hidden
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    {
    "bVisible": false
    }, //ID is hidden
    {
    "bVisible": false
    } //ID is hidden

    ],
    "fnServerData": function (sSource, aaData, fnCallback) {

    aaData.push({ "name": "region", "value": '<%=region%>' });
    aaData.push({ "name": "macId", "value": '<%=macId%>' });
    $.getJSON(sSource, aaData, function (json) {
    /* Do whatever additional processing you want on the callback, then tell DataTables */
    fnCallback(json);
    });

    },

    "aLengthMenu": [[10, 25, 50, 100, 500, 1000, -1], [10, 25, 50, 100, 500, 1000, "All"]]

    }).makeEditable({

    sUpdateURL: "UpdateAdjErrorFile",

    "aoColumns": [

    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,

    null,
    null,
    {
    type: 'textarea',
    submit: 'Save changes',
    rows: 3,
    columns: 10

    }



    ],


    fnShowError: function (message, action) {
    switch (action) {
    case "update":
    //jAlert("Value cannot be empty or can have special characters", "Update failed");
    jAlert(message, "Updated with");
    break;

    }


    }

    and the button click:

    $("button[name=SearchButton]").click(function () {

    var oSettings = oTable.fnSettings();
    oSettings.fnServerData = function (sSource, aaData, fnCallback) {

    aaData.push({ "name": "region", "value": '<%=region%>' });
    aaData.push({ "name": "macId", "value": '<%=macId%>' });

    $.getJSON(sSource, aaData, function (json) {
    /* Do whatever additional processing you want on the callback, then tell DataTables */
    fnCallback(json);
    });


    }


    });
    can any one suggest me whats wrong in my code? how to achieve button click and pass parameters on server side



    });


    });
This discussion has been closed.
All Discussions