I am (very) new to shiny and am trying to create an app where the user can input their choice of one or more options, with their input choice then being used to query an SQL database. For example, if the user selects ‘Dublin’ or ‘Aberdeen’ the app uses RmySQL::dbReadTable() to retrieve the table of the same name from the database and outputs it to a table. If the the user selects ‘Dublin’ and ‘Aberdeen’, the app uses DBI::dbGetQuery() to query the database to select certain columns from each table then join them together, outputting the result to the table.
I can get it to work when there is just one choice selected, but not more than one.
From here Shiny only uses the first item of selectInput when multiple = TRUE I’m assuming that I can access the individual elements of input$dataset when multiple choices are selected.
Having looked here: Get value from reactive context in R shiny based on user input I have tried creating the query using a reactive function and then using this inside the second reactive function as shown below, but the app won’t run and I get this error: Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbGetQuery’ for signature ‘"MySQLConnection", "reactiveExpr"’
library(shiny)
library(rsconnect)
library(RMySQL)
library(dplyr)
library(DBI)
ui = fluidPage(
titlePanel("EWAS database"),
sidebarLayout(
sidebarPanel(
selectInput("dataset", "Choose one or more datasets:",
choices = c("Dublin", "Aberdeen"), selected = NULL, multiple = TRUE),
numericInput("obs", "Number of results to view:", 10),
submitButton("Update View"),
downloadButton("downloadData", "Download")
),
mainPanel(
tableOutput("table")
)
)
)
server <- function(input, output) {
conn <- dbConnect(drv = MySQL(), dbname = "EWAS", host = “xxx”,
username = “xxx”, password = “xxx”)
Dublin <- dbReadTable(conn = conn, name = 'Dublin', value = as.data.frame(Dublin))
Aberdeen <- dbReadTable(conn = conn, name = 'Aberdeen', value = as.data.frame(Aberdeen))
query <- reactive({paste0("'SELECT ",
input$dataset[1], ".", input$dataset[1], "_id, ",
input$dataset[1], ".", input$dataset[1], "_Status_Beta, " ,
input$dataset[1], ".", input$dataset[1], "_Status_SE, " ,
input$dataset[1], ".", input$dataset[1], "_Status_P, " ,
input$dataset[2], ".", input$dataset[2], "_id, " ,
input$dataset[2], ".", input$dataset[2], "_Status_Beta, " ,
input$dataset[2], ".", input$dataset[2], "_Status_SE, " ,
input$dataset[2], ".", input$dataset[2], "_Status_P, " ,
"From ", input$dataset[1], " LEFT JOIN ", input$dataset[2],
" ON ", input$dataset[1], ".", input$dataset[1], "_id ", "= ",
input$dataset[2], ".", input$dataset[2], "_id'")})
custom <- dbGetQuery(conn = conn, query)
datasetInput <- reactive({
if(identical(input$dataset, "Dublin")){
Dublin
} else if (identical(input$dataset, "Aberdeen")){
Aberdeen
} else
custom
})
output$table <- renderTable({
head(datasetInput(), n = input$obs)
})
output$downloadData <- downloadHandler(
filename = function(){
paste(input$dataset, ".csv", sep = "")
},
content = function(file){
write.csv(datasetInput(), file, row.names = F)
}
)
on.exit(dbDisconnect(conn), add = TRUE)
}
shinyApp(ui = ui, server = server)
I’ve also tried putting the query directly in the reactive function (as below), in which case the app launches but I get: internal error in RS_DBI_getConnection: corrupt connection handle.
server <- function(input, output) {
conn <- dbConnect(drv = MySQL(), dbname = "EWAS", host = “xxx”,
username = “xxx”, password = “xxx”)
Dublin <- dbReadTable(conn = conn, name = 'Dublin', value = as.data.frame(Dublin))
Aberdeen <- dbReadTable(conn = conn, name = 'Aberdeen', value = as.data.frame(Aberdeen))
datasetInput <- reactive({
if(identical(input$dataset, "Dublin")){
Dublin
} else if (identical(input$dataset, "Aberdeen")){
Aberdeen
} else
dbGetQuery(conn, paste0("'SELECT ",
input$dataset[1], ".", input$dataset[1], "_id, ",
input$dataset[1], ".", input$dataset[1], "_Status_Beta, " ,
input$dataset[1], ".", input$dataset[1], "_Status_SE, " ,
input$dataset[1], ".", input$dataset[1], "_Status_P, " ,
input$dataset[2], ".", input$dataset[2], "_id, " ,
input$dataset[2], ".", input$dataset[2], "_Status_Beta, " ,
input$dataset[2], ".", input$dataset[2], "_Status_SE, " ,
input$dataset[2], ".", input$dataset[2], "_Status_P, " ,
"From ", input$dataset[1], " LEFT JOIN ", input$dataset[2],
" ON ", input$dataset[1], ".", input$dataset[1], "_id ", "= ",
input$dataset[2], ".", input$dataset[2], "_id'"))
})
And also with the variation below... according to the solution here: How to make Shiny reactivity work with SQL database? but this time the app doesn't run and I get: Error in dbGetQuery(conn = conn, query) : object 'query' not found.
datasetInput <- reactive({
query <- paste0("'SELECT ",
input$dataset[1], ".", input$dataset[1], "_id, ",
input$dataset[1], ".", input$dataset[1], "_Status_Beta, " ,
input$dataset[1], ".", input$dataset[1], "_Status_SE, " ,
input$dataset[1], ".", input$dataset[1], "_Status_P, " ,
input$dataset[2], ".", input$dataset[2], "_id, " ,
input$dataset[2], ".", input$dataset[2], "_Status_Beta, " ,
input$dataset[2], ".", input$dataset[2], "_Status_SE, " ,
input$dataset[2], ".", input$dataset[2], "_Status_P, " ,
"From ", input$dataset[1], " LEFT JOIN ", input$dataset[2],
" ON ", input$dataset[1], ".", input$dataset[1], "_id ", "= ",
input$dataset[2], ".", input$dataset[2], "_id'")
if(identical(input$dataset, "Dublin")){
Dublin
} else if (identical(input$dataset, "Aberdeen")){
Aberdeen
} else
dbGetQuery(conn, query)
})
I was also thinking I could perhaps retrieve the multiple tables individually, then use something like base::cbind() them together using only the columns I want , but I can’t get my head round how I would get that to work in this context.
Finally, I need to be able to expand the app to include many other tables and eventually where the user can have up to 3 options selected with something along the lines of:
if(length(input$dataset) == 1){
dbReadTable(conn, name = ‘input$dataset’, value = as.data.frame(input$dataset)
} else if (length(input$dataset) == 2){
dbGetQuery(conn, query2)
} else if (length(input$dataset) == 3){
dbGetQuery(conn, query3)
}
so I’m really hoping any solution will take that into account. Any help or advice would be very much appreciated!
