JL82559 JL82559 - 3 months ago 36
R Question

shiny r subset factor input

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())
})
})

Answer

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.

Comments