Evaluate filter expression for dplyr when DBI SQL table is used fails

April 18, 2023

I have a SQLite connection where I want to query user-given filters, for example a user might say: e = "hp > 250" and I want to execute this on the database.

If I have a local tibble, I can evaluate the string with data |> filter(eval(parse(text = e))), but when I have a tbl/SQL connection, I receive an error. Error: near "AS": syntax error

To reproduce the use-case, you can use this

library(dplyr)

mtcars |> filter(as.numeric(hp) > 250)
#>                 mpg cyl disp  hp drat   wt qsec vs am gear carb
#> Ford Pantera L 15.8   8  351 264 4.22 3.17 14.5  0  1    5    4
#> Maserati Bora  15.0   8  301 335 3.54 3.57 14.6  0  1    5    8

# this is what I need!
e <- "as.numeric(hp) > 250"
mtcars |> filter(eval(parse(text = e)))
#>                 mpg cyl disp  hp drat   wt qsec vs am gear carb
#> Ford Pantera L 15.8   8  351 264 4.22 3.17 14.5  0  1    5    4
#> Maserati Bora  15.0   8  301 335 3.54 3.57 14.6  0  1    5    8


# works with dplyr, now testing with DBI/tbl/sqlite =============
con <- DBI::dbConnect(RSQLite::SQLite())
DBI::dbWriteTable(con, "mtcars", mtcars)

tbl <- tbl(con, "mtcars")
tbl |> filter(as.numeric(hp) > 250) # works
#> # Source:   lazy query [?? x 11]
#> # Database: sqlite 3.39.1 []
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  15.8     8   351   264  4.22  3.17  14.5     0     1     5     4
#> 2  15       8   301   335  3.54  3.57  14.6     0     1     5     8
tbl |> filter(eval(parse(text = e))) # throws error
#> Warning: Named arguments ignored for SQL parse
#> Error: near "AS": syntax error
tbl |> filter(rlang::eval_tidy(rlang::parse_expr(e))) # throws different error
#> Error in rlang::eval_tidy(rlang::parse_expr(e)): object 'hp' not found

^{Created on 2023-04-17 by the reprex package (v2.0.1)}

Is there any way I can execute this filter using the condition as a string?

>Solution :

You can use tbl |> filter(!!str2lang(e)). The str2lang will turn the string into an expression and the !! will inject the expression into the filter command.

tbl |> filter(!!str2lang(e))
# Source:   SQL [2 x 11]
# Database: sqlite 3.41.2 []
#     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1  15.8     8   351   264  4.22  3.17  14.5     0     1     5     4
# 2  15       8   301   335  3.54  3.57  14.6     0     1     5     8

You could also use tbl |> filter(!!rlang::parse_expr(e)) instead if you want to stick to more rlang functions.