How to use list elements to query data from a database using dplyr?

Question

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.

Waldi · Accepted Answer · 2021-03-11 12:51:05Z

3

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.

edited Mar 11, 2021 at 12:51

answered Mar 11, 2021 at 10:47

Waldi

41.6k6 gold badges38 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatSchu Over a year ago

I get the error: "Error: arg must be a symbol Run rlang::last_error() to see where the error occurred." Tracing the error gives: <error/rlang_error> arg must be a symbol Backtrace: 8. dplyr::filter(...) 10. rlang::quos(...) Run rlang::last_trace() to see the full context. I use dplyr version 0.8.3, which is older than the version of the documentation. Unfortunately, I cannot update the version.

Waldi Over a year ago

for older dplyr versions, try with bang bang : !!input$a

Collectives™ on Stack Overflow

How to use list elements to query data from a database using dplyr?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related