multiple selectInput values create unexpected dplyr (postgres) behavior

Question

I have a lovely Shiny app which accepts selectInput values, queries a postgres DB, and outputs a graph. (It's a simple interface, but hard to reproduce here due to dplyr database connections!)

Today I changed the first selectInput value to multiple=TRUE; updated the variable being passed to the database to be %in% the list returned by the modified control; and all heck broke loose.

Before I select a value the control is null, so I am greeted by the Shiny red "ERROR: RS-DBI driver ... " telling me my query "IN ()" isn't valid
When only one value is selected I get a syntax error, ""locationID" IN 'A1080330' "
I can solve this by putting parentheses around the list returned... locationID %in% (input$rtnLocid)
however this creates a new "operator doesn't exist" error when I select more than one list item because of the added parentheses: " IN (('A1080330', 'B...'))

I think what's going on is the postgres driver always wants SQL list values in parentheses when using IN (some DBs may be more lenient here); adding the parentheses fixes the first selection; the added parentheses break the postgres driver again when multi-select is on.

Can anyone else using Shiny / postgres verify this behavior?

Regards, Jeff

Update: @Steven pointed out this info link in the comments which I hadn't found when I posted: https://github.com/hadley/dplyr/issues/511

Here is a question about this %in% issue in R: stackoverflow.com/questions/27789498/… — nofinator
– nofinator, Commented Jun 6, 2016 at 20:19

Steven Beaupré · Accepted Answer · 2016-11-08 13:32:31Z

4

The problem is the way the query is constructed when you select only one element and use the IN operator. The dplyr translation to SQL does not add the proper parenthesis and thus, fail. This issue was discussed at length here.

One way to work around this is to pass a different instruction to filter() when length of input is equal to 1 (see example below).

Here's what is happening:

tbl(mydb, "iris") %>%
  filter(Species %in% c("setosa", "versicolor")) %>%
  .$query

Gives the proper SQL query syntax:

<Query> SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species"
FROM "iris"
WHERE "Species" IN ('setosa', 'versicolor')
<PostgreSQLConnection>

And, if executed, gives the expected:

#Source: postgres 9.3.13 [[email protected]:5432/csvdump]
#From: iris [100 x 5]
#Filter: Species %in% c("setosa", "versicolor") 
#
#   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#          (dbl)       (dbl)        (dbl)       (dbl)   (chr)
#1           5.1         3.5          1.4         0.2  setosa
#2           4.9         3.0          1.4         0.2  setosa
#3           4.7         3.2          1.3         0.2  setosa
#4           4.6         3.1          1.5         0.2  setosa
#5           5.0         3.6          1.4         0.2  setosa
#6           5.4         3.9          1.7         0.4  setosa
#7           4.6         3.4          1.4         0.3  setosa
#8           5.0         3.4          1.5         0.2  setosa
#9           4.4         2.9          1.4         0.2  setosa
#10          4.9         3.1          1.5         0.1  setosa
#..          ...         ...          ...         ...     ...

Let see what happens if you try to pass a single element:

tbl(mydb, "iris") %>%
  filter(Species %in% "setosa") %>%
  .$query

The query will be:

<Query> SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species"
FROM "iris"
WHERE "Species" IN 'setosa'
<PostgreSQLConnection>

Which, if executed, will result in the following error:

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "'setosa'" LINE 3: WHERE "Species" IN 'setosa') AS "master" ^ )

That's because for a single element, the dplyr translation to SQL query doesn't add the proper parenthesis. Notice how it's 'setosa' instead of ('setosa').

To circumvent that, we can do:

if(length(input$Species) == 1) { 
  tbl(mydb, "iris") %>% 
    filter(Species == input$Species) %>% 
}

Which will build a syntactically valid SQL query:

<Query> SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" 
FROM "iris" 
WHERE "Species" = 'setosa' 
<PostgreSQLConnection>

The following example works around this issue. Here I simply instruct the app to pass filter(Species == ...) if input$Species is of length 1 and filter(Species %in% ...) otherwise.

ShinyApp

server <- function(input, output) {

  selectedQuery <- reactive({

    if(length(input$Species) == 1) { 
      tbl(mydb, "iris") %>% 
        filter(Species == input$Species) %>% 
        .$query
    }
    else(
      tbl(mydb, "iris") %>% 
        filter(Species %in% input$Species) %>% 
        .$query
      )

  })

  selectedData <- reactive({

    if(length(input$Species) == 1) {
      tbl(mydb, "iris") %>% 
        filter(Species == input$Species) %>% 
        data.frame
    }
    else(
      tbl(mydb, "iris") %>% 
        filter(Species %in% input$Species) %>% 
        data.frame
      )
  })

  output$plot <- renderPlot({
    ggplot2::qplot(Sepal.Length, Petal.Length, data = selectedData(), color = Species)
  })

  output$query <- renderPrint({
    selectedQuery()
    })
}

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput("Species", "Species", 
                  tbl(mydb, "iris") %>% 
                    data.frame %>% 
                    .$Species %>% 
                    unique, 
                  selected = "setosa", multiple = TRUE)
    ),
    mainPanel(
      textOutput("query"),
      plotOutput("plot")
      )
  )
)

shinyApp(ui = ui, server = server)

edited Nov 8, 2016 at 13:32

answered Jun 2, 2016 at 14:03

Steven Beaupré

21.7k7 gold badges60 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeff Over a year ago

Thanks Steven -- I'm tweaking this now (I'm not at-ease with R yet, particularly pipes, sorry for the delay). The confusing bit for me is I CAN write a SQL query using IN with only one criteria -- here's an example.

Jeff Over a year ago

I think what was a little surprising for me was that dplyr implicitely controls %in% rather than letting the programmer decide between == and %in% to better match SQL. In SQL this is valid: <Query>SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" FROM "iris" WHERE "Species" IN ('setosa')

Steven Beaupré Over a year ago

@Jeff Sorry if I wasn't explicit enough. The problem is not that a SQL query with IN and a single element is not possible, it's the syntax conversion dplyr does. I'll edit to be more explicit.

Jeff Over a year ago

solid, thanks. Thanks for the github link too. I'll add a kludge for a slider (of integers) below; a different issue with the same origin. This "works", but falls into the "must be a better way" category. crit <- as.numeric(input$int) #slider input from 1 to 50 slider.range <- as.integer(unlist(input$int)) if (slider.range[1] == slider.range[2]) { crit = crit #no change } else { crit = slider.range[1]:slider.range[2] }

Niels · Accepted Answer · 2016-06-07 07:47:42Z

3

Although Steven's answer is perfectly fine, I use a slightly different approach that allows you to use a single pipe. This is convenient when you want to perform other operations as well.

This is what the selectedData reactive would look like:

selectedData <- reactive({
  tbl(mydb, "iris") %>% 
  {
    if(length(input$Species) == 1) 
      filter(., Species == input$Species)
    else
      filter(., Species %in% input$Species)
  } %>%
    data.frame %>%
    mutate() %>%
    etc... 
})

edited Jun 7, 2016 at 7:47

answered Jun 6, 2016 at 12:05

Niels

493 bronze badges

Collectives™ on Stack Overflow

multiple selectInput values create unexpected dplyr (postgres) behavior

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related