DataTables/TableTools serverside from SOLR JSON

crunchfactory

Hi folks,
We're using TableTools for the flash CSV d/l functionality, which works GREAT for getting around older IE versions, but when a user completes a query and receives the records in the table, when they go to get a csv of it, will it return only the records that have been displayed, or will it do an additional ajax request and get ALL of the records from the query from the db? I want them to have them all (from their query) from the db... If this isn't the default functionality, is there a way to make this happen?

Thank you so much, terrific product, and I plan to write up a little piece on the dataTables to servlet to SOLR implementation, as there's very, very little info on this..

Regards,

J

ashiers

When you create a CSV file it is based on all the data that DataTables currently contains on the client side. If your query to the server was for only a subset of all available data, then that is all that will be captured in the CSV. I noticed that you reference Java servlets. There is a library of classes named JED that work with DataTables on the Java platform. I recommend you check it out: http://jed-datatables.ca/jed/

Similar to your scenario, there is an example regarding exporting to an Excel file from DataTables. You may be interested in looking at it as part of your research.
See: http://jed-datatables.ca/jed/examples/exporttoexcel.jsp

If you want to create a CSV of all pertinent records, the easiest solution would be to load DataTables with all those records from a query that asks for just that. If you don't want to do that, you'll likely need to perform a separate ajax request to obtain all those records. How you rangle all those records into a CSV, I'm not sure.

crunchfactory

Thank you! This will be invaluable; we're using a java servlet to be a buffer for SOLR, grabbing data in 100 row batches (each batch takes about 5ms, so even grabbing 5000 rows at that rate is very, very fast, almost mainframe speeds, but displaying in a browser) - I think this exactly what we need to pull it off, so would that mean that sorting, displaying rows via the "Show Entries", and searching/filtering would all be server side? (which is precisely what we're after) - Or, is there a way I can call to it using our current dataTables code?

$('#TableResults').dataTable( {
           dom: 'T<"clear">lfrtipr',
            "length": 10,
            "paging": true,
            "lengthMenu": [ [10, 25, 50, 100, 250], [10, 25, 50, 100, 250] ],
            "scrollX": true,
            "scrollY": "374px",
            "scrollCollapse": true,
            "autowidth": true,
            "tableTools": {
                "sSwfPath": "css/copy_csv_xls.swf",
                "aButtons": [ "copy", 
                              {
                    "sExtends": "xls",
                    "sButtonText": "Excel",
                    "sFileName": "*.csv"
                } ]
            },
            "processing": true,
            "serverSide": true,
            "ajax": function ( data, callback, settings ) {
                   $.ajax( {
                     "url": "/Solr/collection1/select",
                     "data": $.extend( {}, data, {'wt':'json', 'q':'id:*', 'rows':'10'} ),
                     "dataType": "jsonp",
                     "jsonp": "json.wrf",
                     "success": function ( json ) {
                       var o = {
                         recordsTotal: json.response.numFound,
                         recordsFiltered: json.response.numFound,
                         data: json.response.docs
                       };
                       
                       callback( o );
                     }
                   } );
                 },
            "aoColumns": [ 
                a jillion columns, etc....

crunchfactory

Additionally, I'm also concerned about the version of flash the "copy_csv_xls.swf" was created as, we have many IE8 users, and I can't ask anyone to update their flash, it has to happen seamlessly... I'm waiting to find out what specific version their browser is running, hopefully it's 10+ (for security reasons), but on the VM I use for testing IE8, it's running 6.x... so there's a little concern...

ashiers

By default, DataTables handles all searching, sorting, and the number of display of records on the client side. However, in terms of searching and sorting, you can change the default behaviour so that the server handles those. This is particularly advisable when dealing with exceptionally large recordsets. See example: http://jed-datatables.ca/jed/examples/basicssp.jsp to see how server side processing is accomplished using JED with DataTables. I can't comment on the Flash thing. I don't know enough about it myself.

If you plan to do a write up on your research, I'd be interested in reading it. Have you a website where I can follow along?

crunchfactory

Clearly, this would work in a pinch if we can't get around doing it in datatables initialization, sending query strings, etc..., but I'd really like to see if there's a way to pull this off similar to how we're doing pagination, etc..., in that we're putting some of the solr/responseHeader/facet.field info in an array (see above) to keep our pagination together - I'm ok with csv, it keeps things simple, although truly having an *.xslx that wouldn't trigger a warning in Excel is a nice perk.

allan

although truly having an *.xslx that wouldn't trigger a warning in Excel is a nice perk.

I'll get around to it one day :-). It is on my to do list, and something I'd really like to add, but there are a few other things needing to be taken care of first...

I don't want to cross populate threads with the same discussion, but this thread gives more information about the Flash issue you mentioned in the other. Specifically:

but on the VM I use for testing IE8, it's running 6.x...

As in Flash 6?! The API need for the save to local file wasn't added until Flash 10, so there is no "older version" of the TableTools swf that would work - it simply isn't possible.

Allan

crunchfactory

We're using Alan's JED jsp code, it seems like his examples, etc... are possibly a little older, but the logic is all there... is there a list of all the parameters passed when running server side, so we can replace those and get this thing rockin'? We're using dt 1.10.x, or is there another version that would be more appropriate? Also, I notice the th and the td line up great in IE8, but are a little off in IE9; so subtle, but any thoughts on what the issue might be?

allan

Is JED expecting the 1.9 style server-side processing parameters? if so use $.fn.dataTable.ext.legacy.ajax = true; to tell DataTables to send the old style.

Allan

crunchfactory

ok. we're so close to understanding this. When we were connection directly to solr, we were using:

"ajax": function ( data, callback, settings ) {
               $.ajax( {
                 "url": "solr/collection1/select",
                 "data": $.extend( {}, data, {'wt':'json', 'q':'id:*', 'rows':'10'} ),
                 "dataType": "jsonp",
                 "jsonp": "json.wrf",
                 "success": function ( json ) {
                   var o = {
                     recordsTotal: json.response.numFound,
                     recordsFiltered: json.response.numFound,
                     data: json.response.docs
                   };
                   callback( o );
                 }
               } );
             },

but now that were using a servlet, I'm thinking we don't pass a querystring at all, we just call to a servlet:

"ajax": {
            "url": ctxRoot+"/Servlet",
            "dataSrc": "docs"
        },

With Alan's code in the servlet, edited for time:

//COLLECT THE PARAMETERS PASSED FROM THE CLIENT SIDE.
Parameters params = new Parameters();

try{
 String temp = request.getParameter("action");
 if(temp != null)
    params.setAction(temp);
 temp = request.getParameter("bRegex");
 if(temp != null)
    params.setBRegex(Boolean.parseBoolean(temp));
 temp = request.getParameter("table");
 if(temp != null)
    params.setTable(temp);
 temp = request.getParameter("id");
 if(temp != null)
    params.setID(temp);
 temp = request.getParameter("iDisplayStart");
 if(temp != null)
    params.setIDisplayStart(Integer.parseInt(temp));
 temp = request.getParameter("iDisplayLength");
 if(temp != null)
    params.setIDisplayLength(Integer.parseInt(temp));
 temp = request.getParameter("sEcho");
 if(temp != null)

Because our ajax connection goes to the servlet, along with using serverSide and processing: true, etc... we don't have to pass anything else to select row amounts, sort, search, etc.. server side? And then associate the dataTables parameter with the solr parameter? iDisplayLength == rows, essentially?

allan

we don't have to pass anything else to select row amounts, sort, search, etc.. server side?

Should be done automatically. Look at the developer console in your browser to confirm.

Allan

crunchfactory

Hi Allan,
As usual in a scenario like this, we ran our dev server in debug mode, as well as watching the browser console to view request and response headers and bodies; based on that, we got wildly conflicting responses, from both yours and the JED documentation/code, which likely means we're screwing up somewhere. For example, it seemed we should be using "iDisplayLength" to make our request to modify displayed rows, but the only way we could get the select box to update the row count displayed via the servlet was to request length:

String temp = requestParameter(request, "length");
System.out.println("rows: " + temp);
criteria.setRows(Integer.parseInt(temp));

As previously stated, we can't be the first people to this party, so to speak, so it leads me to believe we are doing something radically incorrect here, but by looking at what's being requested in the dom and basing the requests on that criteria seems the only way; this seems a complete brute force method here, but based on this information, maybe you can shed a little light on it?

This is likely what is causing us to require hacking the dom in this case:

we are using:

action=getData 

to make our request, and

criteria=*

to pull our data from the servlet from the jsp/datatables instance:

"ajax": {
            "type": 'POST',
            "url": ctxRoot+"/Servlet",
            "data": {
                'action':'getData',
                'criteria':'*'
            } ,
            "dataType": "json",
            "dataSrc": "docs"
        },

These were made up arbitrarily on our side, and if this is what is causing us to not be able to use documented parameters, what should we be using to make our requests?

crunchfactory

Crap. I just found what we were looking for in the manual. rtfm, indeed. So, yes, length is actually correct. This doesn't make much sense looking at various codebase(s), but there you have it. Thank you!