r - 多个 selectInput 值会产生意外的 dplyr (postgres) 行为

Question

我有一个可爱的 Shiny 应用程序，它接受 selectInput 值、查询 postgres 数据库并输出图形。 (这是一个简单的界面，但由于 dplyr 数据库连接，这里很难重现!)

今天我将第一个 selectInput 值更改为 multiple=TRUE;将传递给数据库的变量更新为 %in% 修改后的控件返回的列表；一切都乱套了。

1. 在我选择一个值之前，控件为空，所以我看到 Shiny 的红色“错误:RS-DBI 驱动程序...”，告诉我我的查询“IN ()”无效
2. 当只选择一个值时，出现语法错误，““locationID” IN 'A1080330'”
3. 我可以通过在返回的列表两边加上括号来解决这个问题...locationID %in% (input$rtnLocid)
4. 然而，由于添加了括号，当我选择多个列表项时，这会产生一个新的“运算符不存在”错误:"IN (('A1080330', 'B...'))

我认为发生的事情是 postgres 驱动程序在使用 IN 时总是希望 SQL 列表值在括号中(有些数据库在这里可能更宽松)；添加括号修复了第一个选择；当多选打开时，添加的括号会再次破坏 postgres 驱动程序。

任何其他使用 Shiny/postgres 的人都可以验证这种行为吗？

问候，杰夫

更新:@Steven 在评论中指出了我在发布时没有找到的信息链接:https://github.com/hadley/dplyr/issues/511

Answer 1

问题在于当您仅选择一个 元素并使用IN 运算符时构造查询的方式。 dplyr 到 SQL 的转换没有添加正确的括号，因此失败。这个问题讨论了很长时间here .

解决此问题的一种方法是，当输入的 length 等于 1 时，将不同的指令传递给 filter()(请参见下面的示例)。

---

这是正在发生的事情:

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

提供正确的 SQL 查询语法:

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

并且，如果执行，给出预期的:

#Source: postgres 9.3.13 [elm@127.0.0.1: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
#..          ...         ...          ...         ...     ...

让我们看看如果您尝试传递单个元素会发生什么:

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

查询将是:

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

如果执行，将导致以下错误:

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" ^ )

那是因为对于单个元素，dplyr 到 SQL 查询的转换没有添加正确的括号。请注意它是如何用 'setosa' 而不是 ('setosa') 的。

为了避免这种情况，我们可以这样做:

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

这将构建语法上有效的 SQL 查询:

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

---

下面的例子解决了这个问题。在这里，如果 input$Species 的 length 为 1 且 ，我只是指示应用程序传递 filter(Species == ...) filter(Species %in% ...) 否则。

---

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)