CODEWITHSUNDEEP
rshiny
user3022875
user3022875

Reputation: 9018

How do you fill dropdown from a database using R Shiny?

I want to fill a drowndown list in an R shiny application with the results of a query from a database.

#I have a global.r file where i put this code:
getData<- function()
{
  ....this function returns a single column of names. I tested that it does work.
}

#Now in my ui.r file I try to use the function to populate a dropdown list like:
source('global.r')
shinyUI(pageWithSidebar(
selectInput("names", "Select Data",getData(),selected="Name 1" multiple = FALSE)
),

When I run this the dropdown is populated with the header of the query but not the entire list that is returned in the query.

Any ideas.

Thank you!

Upvotes: 3

Views: 5113

Answers (2)

conflicted_user
conflicted_user

Reputation: 349

Make sure the columns being retrieved via the query have names.

If you're using dbGetQuery to get your results from the database, the results are returned in the form of a dataframe, even if it is only one column. 

res <- dbGetQuery(conn, 'select column_name from table_name')
is.data.frame(res) # this will be true

The name of the dataframe column is used as a header in the selectInput choices dropdown.
If you do not name the columns in your sql query, then the query header shows up in the selectInput choices dropdown. For example if you perform an operation like max(column_name) vs picking up a direct column.

So instead of this 

get_data <- function(){
  conn <- dbConnect(jdbcDriver, Connection_string)
  on.exit(RJDBC::dbDisconnect(conn))
  q <- "SELECT distinct split_part(column_name, '.', 1) from table_name;"
  return (dbGetQuery(conn, q))
}

try

get_data <- function(){
  conn <- dbConnect(jdbcDriver, Connection_string)
  on.exit(RJDBC::dbDisconnect(conn))
  q <- "SELECT distinct split_part(column_name, '.', 1) my_name from table_name;"
  return (dbGetQuery(conn, q))
}

and notice the difference in the dropdown choices.
Any feedback is appreciated.

Upvotes: 3

klimburg
klimburg

Reputation: 41

If you want the drop down to be reactive use uiOutput("names") instead of selectInput in ui.R. Then in your server.R you want a function like:

output$names<-renderUI({
  selectInput("names", "Select Data", choices=getData(), selected=names[1])
})

http://shiny.rstudio.com/reference/shiny/latest/renderUI.html http://shiny.rstudio.com/reference/shiny/latest/htmlOutput.html

Upvotes: 4

Related Questions