CODEWITHSUNDEEP
rdplyr
Dambo
Dambo

Reputation: 3486

How to filter a remote table based on one single value?

I am doing a filter() using %in% but the way dplyr translates the query seems incorrect. In fact, the %in% operator works fine with more than one value, but it doesn't when only a single element is present. In my original scenario the filtering values are dynamic, thus I would like to have a function that works in both cases.

my_db <- src_mysql(dbname = "dplyr", 
                   host = "dplyr.csrrinzqubik.us-east-1.rds.amazonaws.com", 
                   port = 3306, 
                   user = "dplyr",
                   password = "dplyr")
tbl(my_db, "dplyr") %>% filter(carrier %in% c("UA","AA")) #works
tbl(my_db, "dplyr") %>% filter(carrier %in% c("UA")) #doesn't work

My question is a duplicate of multiple selectInput values create unexpected dplyr (postgres) behavior. Seems like this issue is well-known too

Upvotes: 6

Views: 243

Answers (2)

Dambo
Dambo

Reputation: 3486

Putting together some of the suggestions, the best approach for my scenario it's probably gonna be the one below. The reason why I don't like nesting the filter() in a if statement, is that I have multiple filter from menu items of a shiny app. Thus, manipulating the variable at the source saves me a lot of typing.

a <- c("UA")
b <- if(length(a)>1) a else c(a,"")
tbl(my_db, "dplyr") %>% 
  filter(carrier %in% b)

Or

 a <- c("UA")
 varToFilterFor <- rep(a ,2)
tbl(my_db, "dplyr") %>% 
      filter(carrier %in% varToFilterFor)

Upvotes: 0

dww
dww

Reputation: 31452

I can't offer any insights into why your code fails. But until someone can provide a better solution, here is a simple work-around that provides "a function that works in both cases".

my.carriers <- c("UA","AA")
my.carriers <- c("UA")

if (length(my.carriers)>1) {
  tbl(my_db, "dplyr") %>% filter(carrier %in% my.carriers)
} else {
  tbl(my_db, "dplyr") %>% filter(carrier == my.carriers)
}

Upvotes: 3

Related Questions