CODEWITHSUNDEEP
rdplyrdbplyr
Joanna
Joanna

Reputation: 673

R: dbplyr using eval()

I have a question on how to use eval(parse(text=...)) in dbplyr SQL translation. The following code works exactly what I want with dplyr using eval(parse(text=eval_text))

selected_col <- c("wt", "drat")

text <- paste(selected_col, ">3")

implode <- function(..., sep='|') {
  paste(..., collapse=sep)
}

eval_text <- implode(text)

mtcars %>% dplyr::filter(eval(parse(text=eval_text)))

But when I put it into the database it returns an error message. I am looking for any solution that allows me to dynamically set the column names and filter with the or operator.

db <- tbl(con, "mtcars") %>%
     dplyr::filter(eval(parse(eval_text)))

db <- collect(db)

Thanks!

Upvotes: 2

Views: 400

Answers (1)

Simon.S.A.
Simon.S.A.

Reputation: 6941

Right approach, but dbplyr tends to work better with something that can receive the !! operator ('bang-bang' operator). At one point dplyr had *_ versions of functions (e.g. filter_) that accepted text inputs. This is now done using NSE (non-standard evaluation).

A couple of references: shiptech and r-bloggers (sorry couldn't find the official dplyr reference).

For your purposes you should find the following works:

library(rlang)
df %>% dplyr::filter(!!parse_expr(eval_text))

Full working:

library(dplyr)
library(dbplyr)
library(rlang)
data(mtcars)
df = tbl_lazy(mtcars, con = simulate_mssql()) # simulated database connection

implode <- function(..., sep='|') { paste(..., collapse=sep) }

selected_col <- c("wt", "drat")
text <- paste(selected_col, ">3")
eval_text <- implode(text)

df %>% dplyr::filter(eval(parse(eval_text))) # returns clearly wrong SQL

df %>% dplyr::filter(!!parse_expr(eval_text)) # returns valid & correct SQL

df %>% dplyr::filter(!!!parse_exprs(text)) # passes filters as a list --> AND (instead of OR)

Upvotes: 5

Related Questions