Pagination in shiny

[gerg]

SCENARIO:

I have pagination working in my shiny app using Data Tables. The data set I am dealing with is very large and its bringing the entire records to the table which is causing me memory issues. So I decided to set "paging = FALSE" and to bring up my own action button (NEXT) to fetch the next set of records from database. (For eg: First I am bringing only 50 records from database to the table and when I click NEXT It fetches next 50 records from the database to the table, so I can avoid memory issues since only 50 records is being loaded from database each time).

There is a feature in data table for sorting columns in ascending or descending order on the click of the arrow associated with that column.


With my logic right now this feature will sort only the 50 records that are fetched from the database at that time.

My question is,
So can I get the action in the click on these up and down arrow on each column so that I can write my own logic in server side in fetching the rows that are sorted (ascending/descending) based on that column from the database.

Or are there any other workarounds to achieve my scenario without bringing the entire records to the datatable?

Any help would be appreciated.

[Yihui Xie]

Yes, it is possible to customize the server logic. See the
documentation ?DT::dataTableAjax (in particular, the 'filter'
argument). To understand the default server-side processing in DT, you
may need to take a look at the source code:
https://github.com/rstudio/DT/blob/master/R/shiny.R

Regards,
Yihui

On Tue, Aug 18, 2015 at 1:41 PM, gerg <george...@gmail.com> wrote:
>
> SCENARIO:
>
> I have pagination working in my shiny app using Data Tables. The data set I am dealing with is very large and its bringing the entire records to the table which is causing me memory issues. So I decided to set "paging = FALSE" and to bring up my own action button (NEXT) to fetch the next set of records from database. (For eg: First I am bringing only 50 records from database to the table and when I click NEXT It fetches next 50 records from the database to the table, so I can avoid memory issues since only 50 records is being loaded from database each time).
>
> There is a feature in data table for sorting columns in ascending or descending order on the click of the arrow associated with that column.
>
>
>

[gerg]

Yihui, I appreciate it. That's what I was looking for.

Thank You
Gerg

[gerg]

Yihui-

I was looking into this document http://rstudio.github.io/DT/shiny.html
There is a way to get the column selected in the data table by giving "input$tableId_columns_selected" and to get the cell selected and couple of others.
Is there a way to get the 'q$order' and 'q$columns' values from the 'dataTablesFilter' function just like this way?

On Tuesday, August 18, 2015 at 3:31:19 PM UTC-4, Yihui Xie wrote:

[Yihui Xie]

Currently they are not implemented, but you can file a feature request
to https://github.com/rstudio/DT/issues

Another possible way is the table state info via input$tableId_state.
I'm not sure it gives you everything you want.

Regards,
Yihui

[gerg]

Thanks Yihui.
In the initial post you mentioned we can customize server logic by customizing the filter logic(dataTablesFilter = function(data, params)), but the logic within this dataTablesFilter is on the 'data' which is the entire dataset that is passed to the data table. Customization I am looking is on the 'data'. To make it simple and clear..The datatable will acts as a skeleton and I need to bring the 'data' to the table based on the action on clicks in the table. (For example:: Click on upper arrow in column A($order='asc')will fetch

$length number of records from database sorted in ascending order of column A).

One approach I could think of is to get the action clicks in reactive and bring the data from the database and pass the 'data' to the table.
But how can I change the 'data' that is being passed to the datatable?

Sorry for weird explanation. Can you help with some inputs or some workarounds?

[gerg]

I think the entire explanation I made is revolving on a single concern.
Whole table is being loaded instead of dynamically loading page by page. This will lead to serious memory issues when dealing with huge set of data which brings the entire data to the table.

Any help would be appreciated.

Thank You
Gerg

[Yihui Xie]

The DT default is to put the whole data in memory, but that does not
have to be the case if you customize the filter function. For example,
you may just pass an empty skeleton of the data to DT::datatable()
(which consumes negligible memory), but in the filter function, you
may do SQL queries on a certain database, and that is when the page of
data is really loaded into R. You have all the query variables from
DataTables such as length and page numbers. I'm not sure if I made it
clear enough this time.

Regards,
Yihui

[gerg]

Yihui thanks for reply. Here is the minimal code I tried customizing the DT

 action <- dataTableAjax(session, xx,rownames = FALSE,filter = dataTablesFilterCustom)
  widget <- datatable(xx,
                      rownames = FALSE,
                      class = 'display cell-border compact',
                      filter = 'top',
                    options = list(ajax = list(url = action),serverSide = TRUE,stateSave = TRUE,
                    ,bFilter=0,bInfo=1,

iDisplayLength=5)
  )
 output$x3 = DT::renderDataTable(
   widget
 )

Here xx is the data that I am passing(like iris,mtcars). So what I need to pass over here to get the empty skeleton of the data?

And in the custom filter( dataTablesFilterCustom) q$length,q$start gives me enough data to query.
So what will be the 2 parameters "filter = dataTablesFilterCustom(data,params) " passed to the 'dataTablesFilterCustom'? (I guess data is database connection string and params is how I get 'q' how can I set it default).

[Yihui Xie]

See Section 2 at http://rstudio.github.io/DT/server.html for an
example of what I meant by a skeleton of the data. Basically it is a
zero-row data frame with a few columns. The columns of the data frame
should match what you expect to return from the DataTables query.

That example was based on PHP. For R, your dataTablesFilterCustom is
completely up to you -- it is just a function with two arguments, and
you can do whatever you want inside the function, e.g. (pseudo code)

dataTableAjax(... filter = function(data, params) {
# the data argument in this case may be meaningless
list(
draw = ...,
data = sqlFetch("select whatever from database where this like
that limit 0,10"),
....
)
})

Please take a look at the dataTablesFiler() function in DT:
https://github.com/rstudio/DT/blob/master/R/shiny.R

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/79d5fc2b-485c-49f6-8553-23dd995d48d3%40googlegroups.com.
>
> For more options, visit https://groups.google.com/d/optout.

[gerg]

Thanks Yihui. I really appreciate your effort. Let me try to bring it up.