Multiple queries to posgresql database through R shiny UI input selectors

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, let's say select a name, a type, then the location for a certain data by a user clicking 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 as the data is huge. This is what my initial code looks like after few edits, but it is still not giving me any data. I checked the connection and its working, it just doesn't 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?

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

 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 <- glue::glue_sql(
      "SELECT *                        
       FROM table
       WHERE name IN ({name*})
       AND type IN ({type*})
       AND location IN ({location*});",
      name = name_pick(),                        # CALL reactive FUNCTION  
      type = type_pick(),                        # CALL reactive FUNCTION
      location = location_pick(),                # CALL reactive FUNCTION
      .con = pool)

    outp <- dbGetQuery(pool, query)              # REDUNDANT

  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 )) + 

If you are having trouble querying a database interactively in shiny, then my advice, is to be sure you can succesfully query the database , outside of shiny...

This topic was automatically closed 54 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.