Rshiny Server Side Modularity Using SQL: Filtered Data Table Not Rendering

So far I made a Shiny app with the following procedures/features:

• global.R: Connects to the database using pool in R and retrieves min and max date which will be used in the server side

• ui.R: I created two tabs but will only include tab2 here. tab2 has three dropdown inputs and a filtered data table based on these inputs

  MEDevel.com: Open-source for Healthcare and Education

  Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

  Visit Medevel

• ui_tab2.R: Defined the three inputs explained in ui.R:

  • var_lab_tab2: A static dropdown input with only two choices Choice1 and Choice2
  • daterange_tab2_ui: A date range
  • subid_dropdown_tab2_ui: The last dropdown input that depends on the first two

• server_tab2.R:

  • Function1 dropdownTab2Server:
    • Defined the date range logic with id daterange_tab2
    • Defined the last input dropdown logic with id var_list_tab2
  • Function2 filteredDataTableTab2Server (This part is not working):
    • Fetch the filtered data using SQL based on the three inputs

So far everything is working except for filteredDataTableTab2Server which is returning an empty data table. I think the problem is related to the dynamic sql part inside glue_sql. Any help would be of great help.

##### 1st module: global.R
#### Libraries
library(pool)
library(dplyr)
library(shiny)
library(shinyjs)
library(shinydashboard) 
library(shinycssloaders) 
library(glue) 
library(tidyr)
library(DBI)
library(reactable)
library(tidyverse)

#### Source
source("ui_tab2.R", local = T)
source("server_tab2.R", local = T)

# Assume we made our pooled object and saved it as "pool"

min_max_date <- pool %>%
  tbl("table1") %>%
  summarise(
    max_date = max(timestamp, na.rm = T) 
  ) 

min_max_date_df <- as.data.frame(min_max_date) %>%
  mutate(
    min_date = as.Date("2022-01-01"),
    max_date = as.Date(max_date)
  ) %>%   
  select(c(min_date, max_date))



##### 2nd module: ui.R
dashboardPage(dashboardHeader(
                title = "title",
              ),
              
              dashboardSidebar(
                collapsed = F,
                
                sidebarMenu(
                  menuItem("tab1_title", tabName = "tab1"),
                  menuItem("tab2_title", tabName = "tab2")
                )
              ),
              
              dashboardBody(
                useShinyjs(),
                
                tabItems(
                  tabItem(
                    tabName = "tab2",
                    
                    dropdownTab2UI("dropdown_ui_tab2"), 
                    reactableOutput("table1_tab2"),
                    
                  ) 
                  
                ) 
              )
)



##### 3rd module: ui_tab2.R
dropdownTab2UI <- function(id) {
  ns <- NS(id) 
  
  tagList(
    div(
      shinyWidgets::pickerInput(
        ns("var_lab_tab2"),
        
        "ID:",
        choices = c("Choice1", "Choice2"), 
        
        options = shinyWidgets::pickerOptions(
          actionsBox = T,
          header = "Close",
          liveSearch = T
        ),
        multiple = T
      )
    ),
    
    uiOutput(ns("daterange_tab2_ui")),
    uiOutput(ns("subid_dropdown_tab2_ui"))
    
  )
}



###### 4th module: server.R
function(input, output, session) {
  dropdownTab2Server("dropdown_ui_tab2") 
  
  myvars <- dropdownTab2Server("dropdown_ui_tab2") 

  # This part is not working. The error message is "Error in as.vector: 
  # cannot coerce type 'closure' to vector of type 'character'". 
  # If I remove ```reactive```, then it works but it returns an empty data table.
  data_tab2 <- filteredDataTableTab2Server(
    id = "table1_tab2", 
    input1 = reactive(myvars$var1), 
    input2 = reactive(myvars$var2), 
    input3 = reactive(myvars$var3)
) 
  
  ### renderDataTable
  output$table1_tab2 <- renderReactable({
    reactable(
      req(data_tab2())
    )
  })
}
  


###### 5th module: server_tab2.R
#### 5-1. A dropdown input dependent on the date range
dropdownTab2Server <- function(id) {
  moduleServer(id, function(input, output, session) {
    
    ns <- session$ns 
    rv <- reactiveValues()
    
    output$daterange_tab2_ui <- renderUI({
      req(input$var_lab_tab2) 
      dateRangeInput(
        ns("daterange_tab2"), 
        "Date Range:", 
        start = min_max_date_df$min_date, 
        end = min_max_date_df$max_date
      ) # Retrieved from "global.R"
    })
    
    unique_lists_tab2 <- reactive({
      sql <- glue_sql("
      SELECT
        DISTINCT list AS unique_list
      FROM table1
      WHERE date BETWEEN date ({dateid1_tab2*}) AND date ({dateid2_tab2*})
",
                      dateid1_tab2 = input$daterange_tab2[1],
                      dateid2_tab2 = input$daterange_tab2[2],
                      .con = pool
      )
      dbGetQuery(pool, sql) 
    })
    
    output$subid_dropdown_tab2_ui <- renderUI({
      req(input$daterange_tab2[1], input$daterange_tab2[2])
      shinyWidgets::pickerInput(
        ns("var_list_tab2"),
        "Stations:",
        choices = unique_lists_tab2(),
        options = shinyWidgets::pickerOptions(
          actionsBox = T,
          header = "Close",
          liveSearch = T
        ),
        multiple = T
      )
    })
    
    observe({
      rv$var1 <- input$daterange_tab2[1]
      rv$var2 <- input$daterange_tab2[2]
      rv$var3 <- input$var_list_tab2
    })
    
    return(rv)
    
  }
  )
}


#### 5-2. Filtered data based on all inputs => This part is returning an empty data table
filteredDataTableTab2Server <- function(id, input1, input2, input3) {
  moduleServer(id, function(input, output, session) {
    
    reactive({
      sql <- glue_sql("
      SELECT
        col1,
        col2,
        col3
      FROM table1
      WHERE date BETWEEN date ({dateid_tab2*}) AND date ({dateid_tab2*})
      AND system IN ({listid_tab2*})

",
                      dateid1_tab2 = input1,
                      dateid2_tab2 = input2,
                      listid_tab2 = input3,
                      .con = pool
      )
      dbGetQuery(pool, sql)
    })
    
  }
  )
}

>Solution :

You don’t evaluate your reactive inputs to the filteredDataTableTab2Server module.

Try:

dateid1_tab2 = input1(),
dateid2_tab2 = input2(),
listid_tab2 = input3(),