Hi folks. I would appreciate any help with this. When I filter() using objects inside a list, dbplyr doesn't seem to properly convert into the query. Here is a minimal example:
library(dbplyr)
mf <- memdb_frame(x = letters)
# this works
mf %>% filter(x %in% "a") %>% collect()
# this works
achr <- "a"
mf %>% filter(x %in% achr) %>% collect()
# this doesn't work
alist <- list(a = "a")
mf %>% filter(x %in% alist$a) %>% collect()
You need to explicitly evaluate alist$a in local R session with !! or you can save it to a different variable with something like db_var <- alist$a. Both approaches will work:
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(dbplyr)
#>
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#>
#> ident, sql
mf <- memdb_frame(x = letters)
# this works
alist <- list(a = "a")
mf %>% filter(x %in% !!alist$a) %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `dbplyr_001`
#> WHERE (`x` IN ('a'))