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(
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(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) # as.data.frame() 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 )) +
geom_point()
})
}