I use dplyr (version 0.8.3) to query data. The query includes parameters that are defined in the beginning of the query, as shown in the first reprex below. On top of that, I want to collect the parameters in a list, as shown the second reprex. The first and second examples, which query data from a dataframe, work fine. However, when I want to query data from a database using parameters saved in a list, the SQL translation produces a non-working SQL that cannot be used to query data from a database.
Is there a way to incorporate list entries into a dplyr pipeline to query data from a database?
library(dplyr)
library(dbplyr)
library(RSQLite)
# 1. Data frame: value
a <- 1
mtcars %>% filter(vs == a) # Works
# 2. Data frame: list value
input <- list()
input$a <- 1
mtcars %>% filter(vs == input$a) # Works
# 3. Database: value and list value
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, mtcars, "mtcars", temporary = FALSE)
db_mtcars <- tbl(con, "mtcars")
db_mtcars %>% filter(vs == a) %>% show_query() %>% collect() # Works
db_mtcars %>% filter(vs == input$a) %>% show_query() %>% collect() # Does not work
db_mtcars %>% filter(vs == input[1]) %>% show_query() %>% collect() # Does not work
db_mtcars %>% filter(vs == input[[1]]) %>% show_query() %>% collect() # Does not work
The background of my question is that I want to process and analyze data in a shiny app. I find it easier to develop the code for processing data outside the app and then include the code in the app afterwards. However, this task becomes increasingly difficult with a growing number of inputs. For development, my idea was to define a list named "input" such that I can copy and paste the code into the app. However, I stumble over the problem decribed above. Suggestions for an alternative development workflow are very welcome, too.
For dplyr>=1.0.0 you need to {{embrace}} values, see programming with dplyr :
db_mtcars %>% filter(vs == a) %>% show_query() %>% collect() # Works
db_mtcars %>% filter(vs == {{input$a}}) %>% show_query() %>% collect() # should work
db_mtcars %>% filter(vs == {{input[1]}}) %>% show_query() %>% collect() # should work
db_mtcars %>% filter(vs == {{input[[1]]}}) %>% show_query() %>% collect() # should work
for dplyr <1.0.0 you can use the bang bang !! operator : !!input$a.