1

I want users of a shiny app to query and filter data from a database. Users should be able to flexibly apply optional filters, meaning that unused filters (i.e., filters without input values) should not be processed. The following code gets the job done. However, the server function is somewhat cluttered. The code utilises two if (...) {...} else {...} blocks. I wonder if there is an easier and more concise way to achieve this.

# Libraries

library(shiny)
library(dplyr)
library(DT)
library(RSQLite)
library(DBI)

# Example database

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")

copy_to(con, mtcars, "mtcars", temporary = FALSE)

# User interface

ui <- fluidPage(
    
    textInput(inputId = "input_vs", label = "vs", 
              value = "", placeholder = "Please select one value: 0, 1"),
    
    textInput(inputId = "input_carb", label = "carb", 
              value = "", placeholder = "Please select one value: 1, 2, 3, 4, 5, 6, 8"),
    
    # Eingabe: Start-Knopf
    actionButton(inputId = "go", label = "Start"),
    
    DT::dataTableOutput("output_data")
    
)

# Server function

server <- function(input, output){
    
    # Query data
    
    data <- eventReactive(input$go, {
        
        x <- tbl(con, "mtcars")

        if (input$input_vs != "") { x <- x %>% filter(vs == !!input$input_vs) } else { x <- x }

        if (input$input_carb != "") { x <- x %>% filter(carb == !!input$input_carb) } else { x <- x }

        x <- x %>% show_query() %>% collect()
            
    })
    
    output$output_data <- renderDataTable({ data() })
    
}

# Start shiny app

shinyApp(ui, server)

Is it possible to use something like if (...) {...} else {...} in a dplyr pipeline? Filters should only be processed if input values are supplied by the user. In a sense:

# Does not work

x <- tbl(con, "mtcars") %>% 
  if (input$input_vs != "") { filter(vs == !!input$input_vs) %>% }
  if (input$input_carb != "") { filter(carb == !!input$input_carb) %>% }
  show_query() %>% 
  collect()

Please note that I am aware that, in this example, text input is not the most sensible choice and, for instance, radio buttons would make more sense. In my real life example, however, text input is sensible.

MatSchu
  • 383
  • 1
  • 13
  • Looks like this might help you. https://community.rstudio.com/t/is-it-possible-to-use-ifelse-in-a-dplyr-chain-to-specify-whether-or-not-to-filter-a-data-set/10277 – Humpelstielzchen Jul 06 '20 at 11:48
  • Thanks to your link, I could write a function that I can use in a dplyr pipeline. However, using it in a shiny app results in an error ("Error: invalid argument type"). I suspect that the error has something to with scoping, but I do not know for certain. – MatSchu Jul 06 '20 at 20:42

2 Answers2

1

You can use the "or" operator (|) inside your filter:

x <- tbl(con, "mtcars") %>% 
  filter((!!input$input_vs == "") | (vs == !!input$input_vs)) %>%
  filter((!!input$input_carb == "") | (carb == !!input$input_carb)) %>%
  show_query() %>% 
  collect()

If input$input_vs is empty, the filter will return TRUE for all values in the vector, so the vs filtering won't apply.

Daniel R
  • 1,954
  • 1
  • 14
  • 21
  • Awesome, this works with a slight alteration: you have to add `!!` to `input$input_vs` and `input$input_carb` in the beginning of the two filters. – MatSchu Jul 06 '20 at 20:36
1

Here is one more version (thanks to this Stackoverflow post). To make the dplyr pipeline work, the key is to put the if (...) ... else ... code lines in curly brackets and to make explicit use of . in the filter functions. The SQL translation displayed by show_query() looks concise and very neat.

# Libraries

library(shiny)
library(dplyr)
library(DT)
library(RSQLite)
library(DBI)

# Example database

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")

copy_to(con, mtcars, "mtcars", temporary = FALSE)

# User interface

ui <- fluidPage(
  
  textInput(inputId = "input_vs", label = "vs",
            value = "", placeholder = "Please select one value: 0, 1"),
  
  textInput(inputId = "input_carb", label = "carb",
            value = "", placeholder = "Please select one value: 1, 2, 3, 4, 5, 6, 8"),
  
  # Eingabe: Start-Knopf
  actionButton(inputId = "go", label = "Start"),
  
  DT::dataTableOutput("output_data")
  
)

# Server function

server <- function(input, output){
  
  # Query data
  
  data <- eventReactive(input$go, {
    
    tbl(con, "mtcars") %>% 
      {if (!!input$input_vs   != "") filter(., vs   == !!input$input_vs)   else} %>% 
      {if (!!input$input_carb != "") filter(., carb == !!input$input_carb) else} %>% 
      show_query() %>% 
      collect()
    
  })
  
  output$output_data <- renderDataTable({ data() })
  
}

# Start shiny app

shinyApp(ui, server)
MatSchu
  • 383
  • 1
  • 13