A little app to filter sales by company
library(dplyr)
library(tidyr)
library(DT)
company=c("A","L","S","T","W","A","L","S","T","W")
sales=c(120,140,160,180,200,120,140,160,180,200)
server <- function(input, output) {
output$theCustomersList <- renderUI({
list(
selectInput("customer", "Choose a customer:",
c("A","L","S","T","W")
,selectize=FALSE
,multiple=TRUE
,selected="A"
)
)})
output$result <- DT::renderDataTable(data_frame(company,sales) %>% filter(company%in%input$customer))
}
ui <- fluidPage(
sidebarLayout(
sidebarPanel(
uiOutput("theCustomersList")
),
mainPanel(DT::dataTableOutput('result'))
)
)
shinyApp(ui = ui, server = server)
This works OK as is but if I change it to use a postgresql table as the source of the sales data using src_postgresql I get an error if I only select a single company from the dropdown.
Basically the SQL generated gives the following
Error: RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "'A'" LINE 4: ... AND "company" IN 'A'
Anybody with any ideas?
Update.... this is definitely a database issue. When the filter is applied to an in memory dataframe the "%in%" construct works with either a single selection or multiple selections. When applied to the postgres source the brackets are missed off the resulting sql meaning that the SQL is malformed.
RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "'A'"
LINE 3: WHERE "company" IN 'A'
