How can I pull the sorted data from the datatable?

yatrix

I am trying to create an export-to-csv for my project. How can I pull the data from the table in it's sorted state? aoData is still in the same state as when it was initially loaded. I can't use the flash export, for the record, which is why I'm writing my own.

Thanks in advance!

yatrix

I was able to loop .fnSettings.aiDisplayMaster() and select from .fnSettings.aoData to get the ordered items. Is there any other method?

vanfrankie

We had exactly the same problem: we needed to highlight a particular row in a table with sorting and pagination, thus we have to figure out which page the row is in after sorting.
@Yatrix: you just saved my day!

allan

Please don't use the settings object - the settings are internal and might change between versions :-).

There is an API method call `_` which will get the data in sorted order: http://datatables.net/docs/DataTables/1.9.4/DataTable.html#_ . I've very strongly recommend using that instead.

DataTables 1.10 is going to include a new API which will make this much easier - the new call will be `rows().data()` - but until then, the underscore method can be used.

Allan

vanfrankie

Thanks Allen. But if the table has pagination then seems like underscore can only give me the displayed rows, eg:
$table._('tr', {page: 'all'});
But I need all rows in the sorted order so that I can find which page my target row is in.

psharp

IF you are using ajax to retrieve the data you can:

1. save the state of the request (i.e. sort order / filtering / pagination) in a session.
2. have your excel (or any other export) call your ajax with the saved query parameters and just use that result set. That way, you get just the raw dataset and can do with it what you will.

As an example, our users have a table displayed in front of them. They can select the sort ordering and filter out for a smaller dataset. Smaller could equal 5000+ records. On each request to the controller, I save the query parameters (sent by datatables object) and have a separate controller method called excel_export that uses THOSE parameters, saved off in a session var, to retrieve the data. The excel export view modifies that raw dataset and sends it back to the client. Very simple.

allan

@vanfrankie - Are you using server-side processing? If so, then the current page is all the data that the DataTable has - that's the whole point of server-side processing after all. If not, then please link to a test case showing the problem.

Allan