CODEWITHSUNDEEP
rpostgresqlshiny
Beeba
Beeba

Reputation: 642

Pass user input vector to a function that takes a string

I have a shiny app that takes in user input: input$libraries is a reactive character vector produced by user input from

output$libraries <- renderUI({
checkboxGroupInput(inputId = "libraries", 
                   label = strong("Select the libraries for which you would like to see part counts"), 
                   choiceValues = LibraryIDs$libraryid,
                   choiceNames = LibraryNames$name, 
                   selected = LibraryIDs$libraryid[1],
                   inline = T)}})
})

I would like to select from my postgreSQL database, I have a function set up as such:

get_query <- function(querystring){
  # create a connection

  # loads the PostgreSQL driver
  drv <- dbDriver("PostgreSQL")
  con <- dbConnect(drv, dbname = "RosettaRelational",
                   host = "localhost", port = 5432,
                   user = "postgres", password = rstudioapi::askForPassword("Database password"))
  on.exit(dbDisconnect(con))

  # check for the existance of tables, must be created in pgAdmin4
  #dbExistsTable(con, "libraries")

  query <- eval(parse(text = querystring))
  return(query)
}

It takes in a string and parses it to evaluate the query

now when I try to query the database as such:

Names <- get_query(paste0("con %>% tbl('libraries') %>% 
filter(libraryid %in% input$libraries) %>% select(name) %>% collect()"))

I get the error: object 'input' not found. I know it's not parsing the reactive character vector correctly. How should I change this to get it to work?

I tried:

Names <- get_query(paste0("con %>% tbl('libraries') %>% 
filter(libraryid %in% '",input$libraries,"') %>% select(name) %>% collect()"))

but that only selects the first library in the vector even when the user selects multiple libraries..this works when input$ is only one character, for example when the input is an action button instead of checkboxes

basically what I need is for input$libraries to look like c('111a,'111b','211','311a') when it is passed into the string if user selects 111a, 111b, 211 and 311a, instead of just '111a' which is what it is currently passing.

Upvotes: 1

Views: 101

Answers (1)

Eli Berkow
Eli Berkow

Reputation: 2725

It seems from some testing on my side that your code

Names <- get_query(paste0("con %>% tbl('libraries') %>% filter(libraryid %in% '",input$libraries,"') %>% select(name) %>% collect()"))

will "vectorise" in its current form for multiple libraries in input$libraries. This will create a separate string for each library in input$libraries instead of one string containing all libraries. e.g.

> Names
[1] "con %>% tbl('libraries') %>% filter(libraryid %in% '111a') %>% select(name) %>% collect()" 
[2] "con %>% tbl('libraries') %>% filter(libraryid %in% '111b') %>% select(name) %>% collect()"

Using my own data and your suggestion "for input$libraries to look like c('111a','111b','211','311a')" I adapted your code to

Names <- get_query(paste0("con %>% tbl('libraries') %>% filter(libraryid %in% c(", paste0("'", input$libraries, "'", collapse = ", "), ")) %>% select(name) %>% collect()"))

This should give you your required c('111a', '111b', '211', '311a').

It's not the most elegant but it should work. You could also do that inner paste0() before if it looks messy, as below

libraries_comma_separated <- paste0("'", input$libraries, "'", collapse = ", ")

This will give you '111a', '111b', '211', '311a' and then do

Names <- get_query(paste0("con %>% tbl('libraries') %>% filter(libraryid %in% c(", libraries_comma_separated, ")) %>% select(name) %>% collect()"))

Upvotes: 1

Related Questions