save and reset filters / sorting

[vnijs]

I'm trying to figure out if it is possible to save (and reset) settings provided to DT by a user (this is related to #66).

In my shiny app I use some functions called on session$onSessionEnded to maintain state if a user hits refresh and, more importantly, to restore state from a file. To do something similar with input to DT it seems I would need access to req$rook.input$read() or the parameters parsed in filterFun. Feasible?

I assume (re)setting the values for the DT inputs would be harder. Possible?

[yihui]

I just exposed the state information of the table to input$outputId_state, so you can save it. Then I guess you can restore the state using the stateLoadParams callback to restore the state: http://datatables.net/reference/option/stateLoadParams You also need to specify the option stateSave = TRUE. Please let me know if this works for you. Here is a minimal example demonstrating input$outputId_state:

DTApp = function(data, ..., options = list()) {
  library(shiny)
  library(DT)
  shinyApp(
    ui = fluidPage(
      fluidRow(
        verbatimTextOutput('foo'),
        DT::dataTableOutput('tbl')
      )
    ),
    server = function(input, output, session) {
      options$ajax = list(url = dataTableAjax(session, data))
      # create a widget using an Ajax URL created above
      widget = datatable(data, server = TRUE, ..., options = options)
      output$tbl = DT::renderDataTable(widget)
      output$foo = renderPrint(str(input$tbl_state))
    }
  )
}

DTApp(iris, options = list(stateSave = TRUE))

[vnijs]

This is great @yihui! A few questions/comments:

1. Can input$outputId_state be used in a custom dataTablesJSON function? i.e., are these values available before filtering is done?
2. How to reset if you do want to? An updateDT(..., value = NULL) type function perhaps?
3. If an updateDT type function is possible it could also be used to set initial (new) values I assume.
4. In contrast to the Search field the filter boxes do not 'remember' their values after refresh. The input list does have the previous values but the boxes don't show them.

[yihui]

1. I suppose you can, but I don't see why you have to. I was thinking of using the stateLoadParams callback to restore the state, so when the page is loaded, the previously saved state can be attached to the table, and you don't really need to worry about dataTablesFilter() at all: DataTables will send the correct Ajax request.
   • Another way is you extract the search, order, and page info from the state, and use datatable(options = list(search = list(search = state$search$search), order = ..., )) to create the table.
2. Eventually I think there will be a way to update the table just like leafletProxy() in the leaflet package. For this specific question, I don't know, since DataTables does not seem to support resetting the state of a table. You can always redraw the whole table (this happens when a reactive value changes inside renderDataTable()), although that is not efficient.
3. I don't think you need updateDT(). You can just use the stateLoadParams callback to change the state. Or just redraw the whole table.
4. That is a legitimate issue. You can file a new issue if you want, but I guess it is unlikely to be fixed in the near future since I have got a few more important things to finish for a CRAN release.

[vnijs]

1. I can figure out how to access and use in input$outputId_state variable in dataTableFilter. I know much less about callback and how to determine filter settings in js. If input$outputId_state I can just use R without any need for js.
   1.1 This sounds really interesting. Any chance you could provide an example for "extract the search, order, and page info from the state". Do you mean to pull the information from input$outputId_state variable? Does this mean you can provide a search and order list to the datatable() function directly?
2. Great!
3. See 1.
4. Makes sense

[yihui]

Yes, you can specify the initial search string and ordering info for a table, e.g.

library(DT)
datatable(mtcars, options = list(
  search = list(search = 'Ma'), order = list(list(2, 'asc'), list(1, 'desc'))
))

These info can be obtained from the list input$outputId_state. I'm not sure if that is a little more clear now.

[vnijs]

Yes! That helps a lot. Thanks @yihui

Somewhat related question: Could the DT inputs you created for factors be made accessible as shiny inputs?

[The-Dub]

Hi there,
I've been trying to make saveState works with a reactive dataset, and I don't understand why things are not working properly.

Let's say you have a reactive event that fetches a dataset from a database, a datatable that displays it, and a button that updates that database when you select a row and click on it. For a workflow type of situation, one would want to implement saveState to avoid having to go to page x every time you update the DB and the table is recreated with new values.

When I initialize my table, I have options like pageLength = if (!is.null(claim$state)){stuff$state$length} else {10} to reinitialize the table to the previous state, but it acts in an unexpected way when I update the data behind the scene. Here is a small reproducible example, taken from Yihui previous example.

I grab from the DB with a reactive event (here a reactive copy of the updated myIris dataset), then fetch it to the DT, with saveState activated, and an option to change the pageLength if there exists a static version of the state.

When you select a row and click the 'click me' button, it changes the species of the row, and forces the reactive to re-fetch the data, using the counter. If you change the length and do that several time, you'll notice the pageLength switching to 10, 25 and 50 in a weird, unexplainable behavior.

This behavior happened to me also for other values of the state, like search string or ordering.

myIris <- iris
myIris$Species <- as.character(myIris$Species)

  library(shiny)
  library(DT)
  shinyApp(
    ui = fluidPage(
      selectInput("fooselect", "Change to", choices = c(unique(as.character(iris$Species)))),
      actionButton("foobar", "Click Me"),
      DT::dataTableOutput('tbl')
    ),
    server = function(input, output, session) {
      stuff <- reactiveValues()
      stuff$counter <- 1

      myData <- eventReactive(c(stuff$counter, input$fooselect), {
        # fetch the database
        myIris[myIris$Species != input$fooselect, ]
      })

      output$tbl  <-  DT::renderDataTable({
        datatable(
          myData(),
          selection = 'single',
          options = list(
            displayStart = if(!is.null(stuff$state$start)){stuff$state$start} else {0}, # starting page
            lengthMenu = list(c(10, 25, 50, -1), c('10','25','50','All')),  
            pageLength = if(!is.null(stuff$state$length)){stuff$state$length} else {10}, # page length
            stateSave = TRUE
          )
        )
      })

      observeEvent(input$foobar, {
        stuff$state <- input$tbl_state # saving a static version of the table 

        req(input$tbl_rows_selected)
        # update database
        rowNum <- rownames(myData()[input$tbl_rows_selected, ])
        myIris[rowNum, 'Species'] <<- input$fooselect
        # force to fetch reactive event
        stuff$counter <- stuff$counter + 1
      })

    }
  )

I looked at @vnijs app Radiant to see if he was doing things differently, and I saw that piece of JavaScript in the callback callback = DT::JS("$(window).unload(function() { table.state.clear(); })") and I was wondering what it was doing...

I thought of using a proxy and reloadData, but that wasn't working with the whole database update...
I'm probably doing something wrong somewhere, and i'll gladly appreciate your help on that!

Thank you!

[vnijs]

@The-Dub The callback you noticed is just so I can clear the state of the table when needed. Radiant has a 'refresh' button that resets the app to its original state and I wanted that to work on the DT tables in the app as well. Don't have an answer to your actually question unfortunately. For my purposes, saving and restoring state works nicely with DT in version 0.2 (thanks again @yihui!)

[The-Dub]

I couldn't understand why one of my coworker couldn't reproduce the problem I was experiencing. I did some (intense) digging and pulled half of my hair trying to figure out why that bug was only happening with me.

I realized that the issue doesn't appear on a fresh session, but if you kill the app and relaunch it right away, then things will get screwy. I modified my code in my previous post to add starting page, to better illustrate the example.

On a fresh session, turn the trace on with options(shiny.trace = TRUE).
Launch the earlier code., everything works fine. You can see in the trace that Shiny sends to datatable the correct information, like "displayStart":0 or "pageLength":10.

You can play with it, let's say change the length to 25 and go to page 2. If I then change row 80 by using the "Click Me" button, it works fine. row 80 has been changed to 'setosa', and the table was redrawn with the correct arguments, as the trace shows correctly ("displayStart":25 [...] "pageLength":25).

Then if we kill the app in Shiny and relaunch it right away, the options have been correctly reinitalized. Now if I leave pageLength = 10, but go to page 3, choose row 80 and use the "click me" button, then things get weird.

The trace shows that Shiny is sending the correct information to datatable "displayStart":20, [...]"pageLength":10, but we are now back with a page length of 25, and on page 2...

If you exit the app, and come back, things are getting even more screwy each update.

My take on this issue is that there is probably some kind of hidden object behind the scene that save the state, and one is recreated each time the app is launched, but they accumulate. Then datatable doesn't know which one to choose from and starts behaving like a madman.

I tried to look into the code to find where this issue could be coming from, but no luck...
Since it looks like a bug, not sure if it's from DT or datatable, should I open a new thread, or is this one fine?

Thanks @yihui for looking into this!

[yihui]

Here is a version that uses replaceData() (which is what you really should do instead of resorting to saving state, because that adds quite a bit of complication to the app):

myIris <- iris
myIris$Species <- as.character(myIris$Species)

library(shiny)
library(DT)
shinyApp(
  ui = fluidPage(
    selectInput("fooselect", "Change to", choices = c(unique(as.character(iris$Species)))),
    actionButton("foobar", "Click Me"),
    DT::dataTableOutput('tbl')
  ),
  server = function(input, output, session) {
    myData <- function() {
      # fetch the database
      myIris[myIris$Species != input$fooselect, ]
    }

    output$tbl  <-  DT::renderDataTable(
      myIris,
      selection = 'single',
      options = list(
        lengthMenu = list(c(10, 25, 50, -1), c('10','25','50','All'))
      )
    )

    proxy <- dataTableProxy('tbl', deferUntilFlush = FALSE)

    observe({
      replaceData(proxy, myData(), resetPaging = FALSE)
    })

    observeEvent(input$foobar, {
      req(input$tbl_rows_selected)
      # update database
      rowNum <- rownames(myData()[input$tbl_rows_selected, ])
      myIris[rowNum, 'Species'] <<- input$fooselect
      replaceData(proxy, myData(), resetPaging = FALSE)
    })

  }
)

[The-Dub]

Thanks Yihui, that's great! I knew I wasn't doing things the right way ahah

Sorry for the late response, I encountered some issues trying to implement it.

I switched to your way, and I'm having an issue when server = FALSE (needed in that case to enable download of the data). DataTables throw me an 'Invalid JSON response' error. When looking at the console, DataTables says 'Couldn't find table with id tbl'.

(In your previous code, changing the output$tbl to the following will throw the error:)

    output$tbl  <-  DT::renderDataTable(server = FALSE, {
      datatable(myIris,
        selection = 'single',
        options = list(
          lengthMenu = list(c(10, 25, 50, -1), c('10','25','50','All'))
          )
        )
    })

I think I understand what's going on, since all the data is on the client-side, and that replaceData uses dataTableAjax() behind the scene, that is server-side processing, it breaks. Any solution for that problem?

Another question: what is the main difference between reloadData and replaceData? From looking at the function, it looks like replaceData is pretty much just calling dataTableAjax and then reloadData, but I'm can't really understand why

So the saveState option should be used when the app is reload rather than in the app? Out of curiosity, do you know what was causing the issue with saveState?

Thank you for your help!

[yihui]

The help page ?DT::reloadData has stated that it only works for server-side processing.

dataTableAjax() is used to replaces the previous copy of data stored in the shiny session, and reloadData() calls the JavaScript API ajax.reload() to reload the data in the browser: https://datatables.net/reference/api/ajax.reload() If you don't replace the previous copy of data, you still get the same data when you simply reload the table.

In my opinion, saveState is most useful when you want to save the state before you close the app, and the table is client-side. It is not efficient to restore the state in this way in your case.

The requirement for the table to be client-side makes it tricky to modify the data when the fooselect is present. Either server-side or no fooselect is much easier to deal with. If you want to download the data, another option is to use a separate download button (shiny::downloadButton) instead of DataTables' Buttons extension.

[The-Dub]

Yes, I saw that for reloadData, I was just wondering when I should be using replaceData and when I should be using reloadData.

Thanks for the behind the scenes explanation, it gives me a better understanding of how things work. I still need to figure out some details to implement the reloadData properly. There is something I can't quite figure out on the app I'm working on. (it's a fairly complicated app, and the reloadData doesn't work in this situation, but it works fine with a less complicated app. There must be some interactions I'm not thinking of).

EDIT: O.M.G. I finally figured out why things were breaking... rownames = FALSE. I didn't realized you needed to pass the same argument as datatable(). I feel real dumb right now, one of these days!

Anyway, I just wanted to share a 'dirty' trick to download all the data using the Buttons extension and server-side processing. I pretty much create a proxy button in the dataTable, and when the button is clicked, it simulates a click on the actual shiny download link, using JS. Then, using CSS, I hide the actual download link from the user.

library(shiny)
library(DT)
shinyApp(
  ui = fluidPage(
    DT::dataTableOutput('tbl'),
    tags$style(HTML('#downloadIrisData {display: none;}')),
    downloadLink("downloadIrisData")
  ),
  server = function(input, output, session) {
    # DataTables Output
    output$tbl  <-  DT::renderDataTable(
      iris,
      extensions = 'Buttons',
      options = list(
        dom = 'Bflrtip',
        buttons = list(
          # Buttons download
          # Won't download the entire table because server-side DT
          # Only what is shown on the page (default = 10 records)
          list(
            extend = 'csv',
            text = 'Buttons Download',
            filename = 'buttonsiris.csv'
          ),
          # download by sending signal to shiny
          # Will download the entire table (150 records)
          list(
            extend = '',
            text = '<i> Shiny Download</i>',
            action = JS("function() {document.getElementById('downloadIrisData').click();}")
          )
        )
      )
    )

    # Download Handler
    output$downloadIrisData <- downloadHandler(
      filename = function() {
        'shinyiris.csv'
      },
      content = function(file) {
        write.csv(iris, file)
      }
    )

    # Necessary, because the Shiny download link is hidden
    outputOptions(output, 'downloadIrisData', suspendWhenHidden = FALSE)

  }
)

(I tried to implement the download link directly inside the Button extension (e.g. text = '<a id="downloadManagerData" class="shiny-download-link" href="" target="_blank"><i class="fa fa-download"> Download</i></a>'), but it wasn't working. What I believe happens is that the download button was generated by DT after shiny "reads" the page and binds the input (and adds the shiny-bound-input class, which was missing) and therefore the download URL wasn't populated.)

[yihui]

I thought for quite a while about the download button before when this issue was filed #267, but I didn't come up with an elegant solution. Your trick is very interesting. Thanks for sharing!

[The-Dub]

No problem, glad I can give back to the community!

I don't know enough about how shiny binds its input to R, but if there a way to "force bind" the download button to the output using its ID and class, after the DT table is generated, the download button could be directly implemented in the DT output, without a proxy and a hidden download link. That would probably be a question more for Joe Cheng.