Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in R Programming by (50.2k points)

Can anyone guide me on how to use dplyr::filter with a database when the condition is specified as string?

I am having the following code:

suppressPackageStartupMessages(library(dplyr))

con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

copy_to(con, iris, "iris", temporary = FALSE)

iris_db <- tbl(con, "iris")

filter_str <- "Species == 'setosa'"

And now on that only I am first trying to filter on a dataframe

iris_db %>% collect() %>% filter(eval&#40;parse(text = filter_str&#41;)) %>% count()

#> # A tibble: 1 x 1

#>       n

#>   <int>

#> 1    50

Now after that I am trying the following code on the database using filter_

iris_db %>% filter_(filter_str) %>% collect() %>% count()

#> Warning: filter_() is deprecated. 

#> Please use filter() instead

#> 

#> The 'programming' vignette or the tidyeval book can help you

#> to program with filter() : https://tidyeval.tidyverse.org

#> This warning is displayed once per session.

#> # A tibble: 1 x 1

#>       n

#>   <int>

#> 1    50

Now try this on the database using filter. And it fails... :(

iris_db %>% filter(eval&#40;parse(text = filter_str&#41;)) %>% collect() %>% count()

#> Warning: Named arguments ignored for SQL parse

#> Error: near "AS": syntax error

1 Answer

0 votes
by (108k points)

In R programming you can just simply use the parse_expr from rlang package.

library(dplyr)

iris_db %>% filter(rlang::parse_expr(filter_str)) %>% collect() %>% count()

#      n

#  <int>

#1    50

The above will returns the following query :

iris_db %>% filter(rlang::parse_expr(filter_str)) %>% show_query()

#<SQL>

#SELECT *

#FROM `iris`

#WHERE (`Species` = 'setosa')

Browse Categories

...