reactive shiny objects from database in rhandsontable

Question

I have a large amount of data in a database that I can call using reactive functions in shiny. I would like to present the selected data using rhandsontable, update as necessary and send the data back to the database.

I am running into difficulty when trying to select a reactive object inside another reactive object. I know how to do this with data in memory, as per this example, but as I said, I have a lot of data that would not fit in memory.

See reproducible example below, I would just like to select the different choice options and set the t4 values to F, but the table does not update when I select new reactive data from the drop down menu.

library(shiny)
library(rhandsontable)
library(dplyr)
library(magrittr)
library(RSQLite)
library(DBI)


## create data :
dat <- data.frame("id" = 1:10,
              "choice" = rep(c("option 1", "option 2"), each = 5),
              "t1" = sample(1:100, 10),
              "t2" = sample(1:100, 10),
              "t3" = sample(1:100, 10),
              "t4" = rep("T", 10))

## define database
test_db <- src_sqlite("test_db.sqlite3", create = T)

## copy to database:
test_sqlite <- copy_to(test_db, dat, temporary = FALSE, indexes = list(
  c("choice"),"t1", "t2", "t3", "t4"))

## test data is loaded:
dbGetQuery(test_db$con, paste0("SELECT * FROM dat"))


## build shiny app:

shinyApp(
  shinyUI(
    fluidRow(
  selectInput("select", label = h3("Select box"), 
              choices = list("option 1", "option 2"), 
              selected = "option 1"),
  rHandsontableOutput("hot"),
  actionButton("to_db", label = "Send to Database"),
  verbatimTextOutput("to_db_text")
)),

shinyServer(function(input, output, session) {


## define data to select 
select_dat <- eventReactive(input$select, {
  dbGetQuery(test_db$con, paste0("SELECT * FROM dat WHERE choice = '", input$select, "'"))
  })

# debugging
observe({print(input$select)})  
observe({print(select_dat())})  

values = reactiveValues()

data = reactive({
  if (!is.null(input$hot)) {
    DF = hot_to_r(input$hot)
  } else {
    if (is.null(values[["DF"]]))
        DF = select_dat()
    else
      DF = values[["DF"]]
  }
  values[["DF"]] = DF
  DF
})

output$hot <- renderRHandsontable({
  DF = data()
  if (!is.null(DF))
  rhandsontable(DF, stretchH = "all", selectCallback = TRUE,  readOnly = T) %>%
    hot_col("t4", readOnly = F, type = "dropdown", source = c("T","F"))
})

##    debugging    
observe({print(data())})   

ntext <- eventReactive(input$to_db, {
  ids <- data() %>% filter(t4 == "F") %>% dplyr::select(id) %>% extract2(1)
   sql_str <- paste0("UPDATE dat SET t4 = 'F' WHERE id IN (", paste(ids, collapse=","),")")
   dbExecute(test_db$con, sql_str)

})

observe({print(ntext())})

})
)

Any help with this would be greatly appreciated!

Many thanks

when I was running your code, I got that warning (Warning: Error in eventReactiveHandler: could not find function "dbExecute"); after loading DBI, it seems to work. It prints the table in the console with the changes — MLavoie, Feb 11 '17 at 11:26
Thank you MLavoie, I have added I'm DBI to the packages now :) — anniemaggs, Feb 13 '17 at 14:04
MLavoie, it prints the output from the rhandsontable as part of the debugging I've embedded, but you will see it does not change the "choice" when a different option is selected. The code does not error, but it doesn't do what I want it to do, if that makes sense. — anniemaggs, Feb 13 '17 at 17:54

score 0 · Answer 1 · answered Feb 16 '17 at 14:05

Answered my question using separate observe({}) functions on the reactive object, select_dat(). Using the same inputs as before:

## build shiny app:

shinyApp(
  shinyUI(
    fluidRow(
      selectInput("select", label = h3("Select box"), 
                  choices = list("option 1", "option 2"), 
                  selected = "option 1"),
      rHandsontableOutput("hot"),
      actionButton("to_db", label = "Send to Database"),
      verbatimTextOutput("to_db_text")
    )),

  shinyServer(function(input, output, session) {


    ## define data to select 
    select_dat <- eventReactive(input$select, {
      dbGetQuery(test_db$con, paste0("SELECT * FROM dat WHERE choice = '", input$select, "'"))
    })

    # debugging
#     observe({print(input$select)})  
#     observe({print(select_dat())})  

    ## define data to be updated in rhandsontable:
    values = reactiveValues(data=NULL)

    observe({
      input$select
      values$data <- select_dat()
    })

    observe({
      if(!is.null(input$hot))
        values$data <- hot_to_r(input$hot)
    })


    output$hot <- renderRHandsontable({
      rhandsontable(values$data)
    })

##    debugging    
     observe(print(values$data))   

## send data to database     
     ntext <- eventReactive(input$to_db, {
       ids <- values$data %>% filter(t4 == "F") %>% dplyr::select(id) %>% extract2(1)
       sql_str <- paste0("UPDATE dat SET t4 = 'F' WHERE id IN (", paste(ids, collapse=","),")")
       dbExecute(test_db$con, sql_str)

     })

     observe({print(ntext())})

  })
)

reactive shiny objects from database in rhandsontable

1 Answers1