Modifying Filter and Pagination behaviour in RShiny Datatable

[Nataraj D]

I have a few tables (10-15) that are several million rows long. I'd like to expose them via the DT feature in RShiny with the ability to filter in real-time. However, because it can take a long time to filter a table that is sufficiently large, I'd like to use my own function that can be applied on the filters to retrieve the results (rather than having datatable process the same).

1) Using a custom filter function

As a simple example, if the data was coming from a sql table, t as follows:

a b c 1 2 3 4 5 6 1 2 3

And if I had to find all values of a == 1, I can either use the DataTable filter or instead send a query back - eg., select * from t where a==1. Similarly, select * from t where a==1, b==2, ... etc. The latter method (select from ...) is what I'd like to use. I am using an HPC Timeseries database that could process these queries much faster than using the in-built functionality.

2) I'd also like to be able to interact with the page number so that I retrieve only those rows that are required for the current display. Taking the above table, t as an example, if the user was on page 1, I'd only retrieve the first x rows (eg., 10 rows is Show 10 Entries were selected) and when the user clicks on Page 2, I'd retrieve the next set of 10 rows.

There is a slight caveat in that the pagination happens automatically, but I'd be able to provide the count of rows such that the pagination can be accurate (eg., based on the user filter criteria the count of the table is x rows, and hence show x/10 pagination buttons)

I am not sure if this can be done - whether via datatable or through any other means. Would be grateful if someone could advise further on this. Thanks, Raj.

[Nataraj D]

Ok, so I came quite far using the example at rstudio.github.io/DT/server.html. There is only one outstanding issue in that the filter argument does not seem to be getting passed to the script.

For eg., if we add filter="top" in the code, it doesn't work. Am I using this correctly ?

datatable(employee, rownames = FALSE, filter="top", options = list( ajax = list( serverSide = TRUE, processing = TRUE, url = 'http://datatables.net/examples/server_side/scripts/jsonp.php', dataType = 'jsonp' ) ))

[Yihui Xie]

The column filter is a feature specific to DT. It is not supported by
the DataTables library, and the PHP script does not support it,
either. You have to support it in your own server-side processing
function.

Regards,
Yihui

> --
> You received this message because you are subscribed to the Google Groups
> "Shiny - Web Framework for R" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to shiny-discus...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/shiny-discuss/356f7889-3567-41c7-9cd8-e3fc9a499d90%40googlegroups.com.
>
> For more options, visit https://groups.google.com/d/optout.

[Nataraj D]

Sure, thanks for checking. The global search does work. Also, I don't see any of the filter values being passed on to the script from thd browser. Eg., column[0] search is blank egereas for global it works fine.

[Nataraj D]

To be precise - I can take care of all the server-side processing. The issue is that the values are not even getting passed ... . I can do R, Matlab, Python, etc, but no Javascript (none - which is why I am using RShiny DT) and it'd be great if yourself or someone on this forum could suggest where I could find information on enabling this feature.

For eg., all the column[x][search][value] values are empty.

columns[0][data]  0

columns[0][name]  

columns[0][orderable]  true

columns[0][search][regex]  false

columns[0][search][value]  <--- No Value

columns[0][searchable]  true

columns[1][data]  1

columns[1][name]  

On Sunday, April 24, 2016 at 1:16:49 AM UTC-4, Nataraj D wrote:

Sure, thanks for checking. The global search does work. Also, I don't see any of the filter values being passed on to the script from thd browser. Eg., column[0] search is blank egereas for global it works fine.

> email to shiny-discuss+unsubscribe@googlegroups.com.

[Yihui Xie]

It should not be empty if you enabled the column filters and have
typed in the search box of the first column. I have no idea why it was
empty in your case.

Regards,
Yihui

>>> > email to shiny-discus...@googlegroups.com.

[Nataraj D]

You can see the issue using the exsmple at http://rstudio.github.io/DT/server.html

(Example 2 JSONP)

The change I made was to add filter="top" expecting that the respective values would be passed when typed into the boxes which didn't happen.

datatable(employee, rownames = FALSE, filter="top", options = list( ajax = list( serverSide = TRUE, processing = TRUE, url = 'http://datatables.net/examples/server_side/scripts/jsonp.php', dataType = 'jsonp' ) ))

We eventually got it to work using yadcf, but the above didn't work at least when me and my colleagues tried. If it is working for you, something is amiss ... .