Conditional filtering using dbplyr in Shiny

shiny
1

Hi all,

I am working on a business problem where I would like to filter data from a database in a shiny app based on user-supplied input from a list.

Here is the snippet of what works only after collecting the data from a database, which is computationally expensive.

reactive({
my_database_data %>%
collect() %>%
filter(
      if (!!input$list != 'All')
        (branch == !!input$list
        ) else TRUE)
})

I would like to achieve the same but doing so straight from the database.

reactive({
my_database_data %>%
filter(
      if (!!input$list != 'All')
        (branch == !!input$list
        ) else TRUE)
})

Not too sure of the if-else statement above.

2

So you have two questions. one is about how to collect 'later' (post filtering) and the other is about your if statement.

probably avoiding early collection will be easiest as I think its as simple as moving it to after the filter.

as far as your filter conditions go, assuming that input$list might return multiple values for matching on branch, then the %in% operator should be used rather than ==

3

Hi @nirgrahamuk ,

When I collect as shown in the first code snippet, I am achieving the target results, that is when All is selected, no filter is applied, and when an option I would like to be selected to show information from that branch, that works too.

Secondly, the user input will be selected one at a time, and not as multiple options.

4

Ok, so probably your if is fine, and you will succeed by moving the collect to the end

5

@nirgrahamuk ,

when I run the second code snippet I am getting this error. Therefore, collecting early avoids running into this error.

Warning: Error in : abbr is not supported in SQL Server translation

6

if you change collect to show_query what is your query in this circumstance ?

7

The translated SQL snippet provided here.

WHERE ((IIF('All' != 'All', ("branch" = 'All'), TRUE))

8

what sort of DB are you using ?
In my example it works fine, but it doesnt translate to IFF

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, iris)

iris2 <- tbl(con, "iris")
iris2

input <- list()

input$species_choice <-'All'
# input$species_choice <-'setosa'

summary <- iris2 %>% 
 filter(if(!!input$species_choice!="All" )(Species == !!input$species_choice) else TRUE )

show_query(summary)
collect(summary)
9

I am using Microsoft SQL Server.

10

Hopefully someone with experience in that can help you

11

Unless I'm misunderstanding, you could simply move the if statement outside of the filter:

reactive({
  # If 'All' not selected, filter
  if (input$list != 'All') {
    
    filter(my_database_data, branch == !!input$list) %>%
      collect()
    
  } else {
    
    collect(my_database_data)
    
  }
})
12

@cnbrownlie my intention is not to collect at this stage. I'm collecting after an aggregation summary is done and data passed to ggplot2.

The reason I would want the filter to work at this point without using collect().

13

In that case you could just remove the collect()s from my answer to have the reactive expression return the lazy query itself:

reactive({
  # If 'All' not selected, filter
  if (input$list != 'All') {
    
    filter(my_database_data, branch == !!input$list)
    
  } else {
    
    my_database_data
    
  }
})
14

Thanks. This worked like a charm.

1 Like
15

Please check the this package GitHub - cardiomoon/dplyrAssist, as it contains the same syntax for dbplyr (almost) you can easily twick it to work with databases as well.
The widget will give yoou the list sort of ui that you are looking for.
Here the vignette RPubs - Document

16

Hi Hope this is of some use to you. complete R script is available in the link provided in the description

17

You can add a conditional filter like the code below. Wrap this non-trivial statement in curly brackets and make sure you first pass the data frame (using '.') to the filter function. For additional data manipulation, just add a pipe after the closing bracket. Obviously, this would not be executed on the database.

my_database_data %>%
{ if (input$list != 'All') filter(., branch %in% input$list) else . }

To run it on the database, I would generate a query that depends on input$list using paste.

18

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.