Mysql equivalent to Datatables search()

Tronik

Since my table has server-side enabled Im creating a stand-alone server side script which exports to excel.

Basically I fetch data from mysql and use phpspreadsheet for excel creation.

Naturally I want the excel being exported to be identical to the current table shown client-side.

Im not sure how to best approach that,
I know that I can get alot from DT API, columns visible for example,
But how can I make a mysql query which returns the exact same rows as a search made in datatables (the regular standard search input)

I know search() is more complicated than ’=’ and ’%LIKE%’

Happy easter

allan

Hi,

The way search in the demo implementation for server-side processing for DataTables works is actually just a %LIKE% per column - see the code here.

The client-side search implementation is a bit more complex than that, but it doesn't sound like you are using that.

So assuming you are using our demo SSP script, or something like it, you could use the method shown in the SSP class.

Another option would be to have the client-side send the list of IDs of the rows to export, and then do a WHERE ... IN ... for your export. That would ensure an exact match between what is shown and the server-side export.

Allan

Tronik

Thank you Allan,

Ok I see, didnt know that server-side search was different from client side.
Yes I thought of that too, sending ID’s, but might be slow searching 20k ids using IN.
Will use LIKE for now and compare rows.

Another thing, when using columns().visible() I only get the column IDs, is there a built in method for retrieving column names?
I found a workaround which worked, but thought I ask anyway.

allan

You could use this little plug-in:

$.fn.dataTable.Api.register( 'columns().names()', function () {
    return this.iterator( 'column', function ( settings, column ) {
        return settings.aoColumns[column].sName;
    }, 1 );
} );

And call like: table.columns().names().

I'm assuming you are referring to a name as in columns.name?

Allan

Tronik

No, sorry, I needed the column name as in what the data name is set as.

This worked:

var columns = table.settings().init().columns;
var visible_columns = [];
                        
table.columns().every( function(index) {
                            
if (this.visible()) {
                                     
 visible_columns.push(columns[index].data);
            }
})

Tronik

After some testing I looking into maybe using WHERE id IN after all, it looks lite mysql can handle it.

To get the IDs I use:
var filtered_ids = table.rows( { page: 'all', search: 'applied' } ).ids().toArray();

However I also use Scroller, and I know the data rendered in viewport is set by displayBuffer setting.
This is why Im creating a stand-alone excel-export in the first place.

Is there ANYWAY I can get the filtrered IDs (all of them) when using scoller and server-side? Although Datatables has counted them.

Or do you have any other suggestion what to do? The reason Im using scroller is that I have an image field on each row and it is uneccessary that 30k rows with images is loaded each time for no use. (cpu load on server)

kthorngren

var filtered_ids = table.rows( { page: 'all', search: 'applied' } ).ids().toArray();

This will only find the rows that are at the client. Instead of trying to use the client to get the full set of row IDs may you can send, via ajax, the current searches in place to the server so your script fetching the Excel data can use that for the select statement. You can use search() and if appropriate columns().search() to get the search terms used.

Kevin

Tronik

Yes, thats the approach I took.
Just wanted to ask about the possibility of getting all filtrered IDs, since it would be a little bit (a lot) easier and more bulletproof.
It took some coding to get search(), searchPanes filter, and columns.visible() and cook all that down to a custom made mysql query.
But i think I'm almost there