Making multiple queries to posgresql database through R shiny UI

Question

I am building a Shiny dashboard that will allow me to select certain data from my postgresql database. I am new to shiny and postgresql so I appreciate your input on this. I am aiming at having a dynamic input from User, say click to select a name, a type, then the location for a certain data. And that the user can select it from a 3 selectinput widgets. The process of selecting the items will allow query of the data in the database. Lets say I have 7 names, 2 types and 3 locations.. so selecting a name, then a type, then a location will give me a part of the data. This is what my initial code looks like, but it does not give me any data showing up. I checked the connection and its working, it just doesn't can not find the data I am looking for plotting and displaying so I get an empty dashboard with no table or plot. Any idea how to go about this?

library(shiny)
library(shinydashboard)
library(RColorBrewer)
library(leaflet)
library(dplyr)
library(ggplot2)
library(tidyverse)
library(fpp)
library(plotly)
library(sp)
library(RPostgreSQL)
library(DBI)
library(pool)

#####


pool <- dbPool(drv = dbDriver("PostgreSQL", max.con = 100), user = "user", password = "pswd", host = "localhost", port = 5432, dbname = "people", idleTimeout = 3600000)

##list of people names
All<-tbl(pool, "table")
All%>%group_by(name)
names_All<- dbGetQuery(pool, "SELECT name FROM table GROUP BY name") ## list of names another way
type_grouped<- dbGetQuery(pool, "SELECT type FROM table GROUP BY type")
loc_grouped<- dbGetQuery(pool, "SELECT location FROM table GROUP BY location") 

####

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput(
        inputId = "name",
        label = " name",
        choices = names_All,
        selected = 1,
      ),
      selectInput(
        inputId = "type",
        label = " type",
        choices = type_grouped,
        selected = 1,
  
      ),
      selectInput(
        inputId = "location",
        label = " location",
        choices = loc_grouped,
        selected = 1,
      )
    ),
    mainPanel(
      tabsetPanel(
        tabPanel("graph", plotOutput("plot")),
        tabPanel("Table", dataTableOutput("table"))
      )
    )
)
)

server <- function(input, output, session) {

  selectedData <- reactive({
    req(input$name)
    req(input$type)
    req(input$location)
    query <- glue::glue_sql(
      "SELECT * FROM table
       WHERE name IN ({name*})
       AND type IN ({type*})
       AND location IN ({location*});",
      name = input$name,
      type = input$type,
      location=input$location,
      .con = pool)
    outp <- as.data.frame(dbGetQuery(pool, query))
    outp

  })

   
  output$table <- DT::renderDataTable({
    DT::datatable(  data = selectedData(),
                   options = list(pageLength = 14),
                    rownames = FALSE)
  
  
  output$plot <- renderPlot({
    ggplot( data = selectedData(), aes(x = date, y = name )) + geom_point()
  })
  
}

shinyApp(ui = ui, server = server)

App running result

Answer 1

Consider integrating a submit button to trigger processing of server code via an eventReactive call. This even helps user understand the flow of form controls and processing. Also, right now you use SQL's IN redundantly since input choices are only single values. But use multiple=TRUE argument in selectInput() to adjust that (with note to user).

User Interface

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      selectInput(
        inputId = "name",
        label = " name",
        choices = names_All,
        selected = 1
      ),
      selectInput(
        inputId = "type",
        label = " type",
        choices = type_grouped,
        selected = 1  
      ),
      selectInput(
        inputId = "location",
        label = " location",
        choices = loc_grouped,
        selected = 1
      ),
      actionButton("submit", "Submit")        # NEW SUBMIT BUTTON
    ),
    mainPanel(
      tabsetPanel(
        tabPanel("graph", plotOutput("plot")),
        tabPanel("Table", dataTableOutput("table"))
      )
    )
  )
)

Server Function

server <- function(input, output, session) {

  name_pick <- reactive({ req(input$name) })
  type_pick <- reactive({ req(input$type) })
  location_pick <- reactive({ req(input$location) }) 

  selectedData <- eventReactive(input$submit, { # INITIATE ON submit
    query <- "SELECT *                        
              FROM table
              WHERE name = ?name
                AND type = ?type
                AND location = ?location;"

    stmt <- DBI::sqlInterpolate(pool, 
                                query,
                                name = name_pick(),
                                type = type_pick(),
                                location = location_pick())

    outp <- dbGetQuery(pool, stmt)
  })

  output$table <- DT::renderDataTable({
    DT::datatable(data = selectedData(),
                  options = list(pageLength = 14),
                  rownames = FALSE)
  })

  output$plot <- renderPlot({
    ggplot(data = selectedData(), aes(x = date, y = name )) + 
       geom_point()
  })
  
}

Also, try avoiding SELECT * and explicitly define needed table and graph columns in specific order to control output of your Shiny result. Plus, it helps with readability and maintainability of code.