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

When you state names_All<- dbGetQuery(pool, "SELECT name FROM table GROUP BY name") where is table defined? Also, you have a typo: req(inputname) should be req(input$name) — YBS
– YBS, Commented Dec 31, 2020 at 5:38
Your SQL is not correct as you have two WHERE clauses which should have raised an error. Check console for messages. — Parfait
– Parfait, Commented Dec 31, 2020 at 5:44
@ YBS The table is the name of my table in the database 'people' specified in pool. So I don't have to declare it as a variable. Thanks for the note on the typo, I must have accidently delete it while typing in, but its all corrected now still no data. @Parfait its also a typo I just fixed it and still no data showing. — rains
– rains, Commented Dec 31, 2020 at 6:21
Please edit your post with actual code and not typos. We need to see how you fixed it. Also, post any errors that output to console. — Parfait
– Parfait, Commented Dec 31, 2020 at 15:17
@Parfait thanks for pointing out I can edit the post. I had no idea I can do that, I fixed the typos. And re-run the code, nothing happened I get an empty table and plot. The selector choices are working though as I can see the options. But that does not change the data I want to select from the database. I attached the image in the edited post in the App running result link above. — rains
– rains, Commented Dec 31, 2020 at 15:47

Parfait · Accepted Answer · 2021-01-03 19:46:12Z

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.

edited Jan 3, 2021 at 19:46

answered Dec 31, 2020 at 16:34

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

rains Over a year ago

Thank you Parfait. I managed to edit my code as per your comment. I can see changes happening the console when I click submit. However, I got this error, Warning: Error in eval: attempt to apply non-function. Also just double checking, I think there is a missing bracket the line below outp after }) ) I was only able to run it after adding this one. Finally, the reason I am using SELECT , is because I want all the column names , when I tried typing it in I got a syntax error. If I shall provide them all, would using *SELECT name, etc FROM table work ?

Parfait Over a year ago

Actually there should not be an extra closing parentheses since I had an extra opening. Please try again with my edit and describe any error or undesired results not simply it worked or it did not work. Syntax error may be due to column names with spaces or special characters. Enclose such column names with double quotes using all lowercase.

rains Over a year ago

I have copied your code and use it as is. I got the above error attached as image in the post. I had to adjust location = location(), to location = location_pick (), but kept the rest the same as your code. Let me know what you think.

Parfait Over a year ago

Error derives in UI section. See edits removing unneeded commas with small server fct fix.

rains Over a year ago

Thanks, I did the edits. Now I am getting this error message Error:'data' must be 2-dimensional (e.g. data frame or matrix) , where can I convert my table from the database to a data frame?

|

Collectives™ on Stack Overflow

Making multiple queries to posgresql database through R shiny UI

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related