Dambo Dambo - 4 months ago 13
R Question

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

Answer

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)