shiny r subset factor input

Question

The STORENUMBER filters the data and renders the map and table below, but the DMA doesn't. Does subset() work differently on factors than integers in server.r?

data

STORENUMBER = c(123,456)
DMA = c("LA","SD")
LATITUDE = c(130, 132)
LONGITUDE = c(30,35)
locations = data.frame(STORENUMBER, DMA, LATITUDE, LONGITUDE)

ui.r :

       tabItem(tabName = "control",
            fluidPage(
              titlePanel("Control Center"),

              fluidRow(
                  # the Stores are integers
                  column(6,
                      helpText("Test Stores"),                                  
                        # test stores
                        selectInput("testStores", 
                                    label ="Test Stores",
                                    choices = as.vector(unique(locations$STORENUMBER)),
                                    selected = NULL,
                                    multiple = TRUE)
                      ), 
                  # the DMAs are factors
                  column(6,
                      helpText("Test DMA"),
                      selectInput("tDMA", 
                                  label ="Test DMAs",
                                  choices = as.vector(unique(locations$DMA)),
                                  selected = NULL,
                                  multiple = TRUE)
                      ) #column
                  ), #fluidRow


              fluidRow(
                titlePanel("Map"),
                leafletOutput("map"),
                p(),
                actionButton("recalc", "New points")
                ) , 


              fluidRow(
                titlePanel("Test Store Table"),
                column(12,
                       DT::dataTableOutput("tableteststores")
                )  
              )

              ) #fluidPage
            )

Here is the server.r script showing the subset() function.

server.r:

shinyServer(function(input, output){
  # not sure why DMA isn't working 
  tstores <- reactive({
     subset(locations, DMA %in% input$tDMA | STORENUMBER %in% input$testStores)
    })


  # table of locations
  output$tableteststores <- DT::renderDataTable(DT::datatable(
    data <- as.data.frame(tstores())
  ))  

  # map
  output$map <- renderLeaflet({
    leaflet() %>%
      addProviderTiles("Stamen.TonerLite",
                       options = providerTileOptions(nonWrap = TRUE)
                       ) %>%
      addMarkers(data = tstores())
  })
})

You haven't supplied any test data so this problem isn't [reproducible](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). What exactly do you mean that it "doesn't render"? Are you getting an error? Is your select showing the DMA values you expect or is it showing you numbers? — MrFlick, Aug 16 '16 at 21:32
Added test data so now is reproducible. I can see input objects in form field, but when select a DMA, the map object and tableteststores object doesn't return data. When select STORENUMBER the map and tableteststores return objects. So it appears the subset() function works for the integers STORENUMBER but doesn't work for the factors DMA. Open to your thoughts. Thanks. — JL82559, Aug 16 '16 at 22:05
I can't replicate any sort of error with the code you've provided. Filtering by DMA seems to work just fine. — MrFlick, Aug 16 '16 at 22:19

score 0 · Accepted Answer · answered Aug 18 '16 at 18:25

The data is queried with a SQL statement in the reactive() function. When passing factors as "input" variables in the WHERE clause of the SQL statement R passes a vector of factors within double quotes like ("this", "that", "then") but for the SQL to execute it needs the factors to be passed with single quotes like ('this', 'that', 'then') in the WHERE clause. Consider writing the input variables like this to replace the double quotes with single quotes if planning to use SQL within the reactive() function.

library(RODBC)    

myconn <- odbcConnect('server', uid="user", pwd="password")   

reactive({
  data <- as.data.frame(sqlQuery(myconn, 
        paste(
             "SELECT
                  STORENUMBER
                  ,DMA
                  ,LATITUDE
                  ,LONGITUDE
             FROM database.datatable
             WHERE DMA in", 
                          #this is a way to replace double quotes as single quotes# 
                          #when passing a list or vector of factors# 
                          cat("('",paste(input$DMA, collapse="','"), "')"), "
             OR STORENUMBER in", 
                          # the issue doesn't appear when passing integer types#
                          input$STORENUMBER)  
})

Although not shown in the question, this appears to have been the issue with my code. As the comments above explain, the code in the question works fine. It's only when trying to do the SQL in a reactive() function that the code fails. The reason is explained in this answer and a solution is shown here. Hope this helps.

shiny r subset factor input

1 Answers1