Get filtered data in exportable format using serverside:true

mrdansk

Link to test case: Apologies but I am not able to give access to a test case because the data is not available outside of my work.
Debugger code (debug.datatables.net): N/a
Error messages shown: N/a
Description of problem:

I am trying to get filtered data from my DataTable in an exportable format. For example:

If I have 55,000 rows
I filter these rows down by records containing NULL and get 1,000 rows
I want to be able to export these 1,000 rows (or at least send them to a PHP script in JSON/Array format)
When using the built-in Buttons export, I only get the visible rows (Let's say that is 11 rows per page)
I am using the Scroller plugin so as I scroll I will get another 11 rows etc.

I haven't been able to find a way of getting the full 1,000 filtered rows via table.rows.data() or any other method so far.
I can get the number of rows via the table.page.info() call but not the actual data.

I want to be able to take these filtered rows and export them to CSV (which once I have the data I can do this serverside).

Just to reiterate, due to the number of records shown I need to have serverside on otherwise the table goes extremely slow.

Am I missing an easy solution here? Or can someone point me to an example or provide one?

Any help appreciated!

Thanks
Dan

rf1234

You can only export what you have downloaded to the client. If you use extensions like scroller, serverSide and what have you that prevent all data from being downloaded you can't export them because they don't exist on the client.

Either you download all the data, filter and export them or you send the filter to the server and read the filtered data on the server directly.

rf1234

Just to reiterate, due to the number of records shown I need to have serverside on otherwise the table goes extremely slow.

You could post filter variables to the server like this and then retrieve the data on the server. "d.search.value" is the filter from the search field. But you can pass a lot more data if you like. Advantage: You don't have to download everything to the client.
Please take a look at this too:
https://datatables.net/manual/server-side#Sent-parameters

var table = $('#yourTable').DataTable({
    serverSide: true,
    ajax: {
        url: 'yourServerScript',
        type: 'POST',
        data: function ( d ) {   
            d.userFilter = d.search.value;
            d.userStartDate = $('#monthsSelect').val();
            d.whatever = ...
        }
    },

In your server script you can use these variables like this:

$_POST['userFilter'] ... do something
$_POST['userStartDate'] 
$_POST['whatever'] 

//run SQL using the $_POST variables in the WHERE clause
//save csv file based on query results and whatever

Alternatively you can build a custom button and do the ajax call on button click passing the search value and other parameters to the server.

Getting the value from the search field goes like this:

var userFilter = table.search();

Here is a custom button that
- sends a request to the server
- downloads the data retrieved on the server to the client (in this case a zip archive)
- sends a request to the server to delete the temporary zip-file that had been saved on the server in the first step.

//custom button to download all ctr management documents of one selected installation
$.fn.dataTable.ext.buttons.downloadInstDocs = {
    //only enabled when one row is selected (like edit / delete)
    extend: 'selectedSingle', //alternative would be 'selected' (multiple rows)
    name: "downloadInstDocsButton",
    text: downloadInstDocsLabel,
    action: function ( e, dt, button, config ) {
        var selected = dt.row( {selected: true} );
        if (selected.any()) {
            $.busyLoadFull("show");
            $.ajax({
                type: "POST",
                url: 'actions.php?action=zipInstDocuments',
                data: {
                    ctrInstId: selected.data().ctr_installation.id
                },
                dataType: "json",
                success: function (data) {     
                    window.location.href = data.zip;
                    $.busyLoadFull("hide");
                    setTimeout(function () {
                        $.ajax({
                            type: "POST",
                            url: 'actions.php?action=deleteZipInstTmpFile',
                            data: {
                                deleteWebPath: data.zip
                            }
                        });
                    }, 10000);
                }
            });
        }
    }
};

mrdansk

That's interesting I will look into the sent parameters. Does that work if you have multi-column filtering as well?

I have a search box for each column in the header in my case.

That would at least take the load off the client side and allow me to work on an export solution on the PHP side!

Thanks for the help rf1234

rf1234

Does that work if you have multi-column filtering as well?

Why not? You would just need to get the content of those search fields.

I have a tr that has those search boxes for every column as well. All the td's have class "filterHead".

You can loop through this and get the search values entered by the users. Haven't tested the code below though ...

var table = this.api();
var userFilterArray = [];
$('.filterHead', table.table().header()).each( function () {
    userFilterArray.push( $(this).val() );
} );