CODEWITHSUNDEEP
mysqlshinysqldf
R noob
R noob

Reputation: 513

Selecting values from one column and passing corresponding values from another column in MySQL queries in Shiny

I have a Shiny app querying large data from a MySQL database but for purposes of this question I will use SQLDF which is similar in syntax on Shiny environment.

A mock up of my app is as below:

library(shiny)
library(dplyr)
library(sqldf)
library(DT)
library(stringr)

    df <- data.frame(empName = c("Jon", "Bill", "Maria"),
                    empID = c("J111", "B222", "M333"),
                     empAge = c(23, 41, 32),
                    empSalary = c(21000, 23400, 26800)
                    )


   shinyApp(

      ui = fluidPage(

                    selectizeInput("Search", label = p("Select name"), 
                          choices = as.character(df$empName),
                           multiple = TRUE),
                    hr(),
          fluidRow(
              column(6, DT::dataTableOutput("table1")), 
              column(6, DT::dataTableOutput("table2"))),
          hr(),
          hr(),
         fluidRow(
                column(6, DT::dataTableOutput("table3")),
                     column(6, DT::dataTableOutput("table4"))
                 )),



      server = function(input, output, session) {  
           output$table1 = DT::renderDataTable({ df }, options = list(dom = 't'))
              

        df2 <-  reactive ({ 
            (df %>% filter(empName %in% input$Search)%>% select(empID))  
                   })

      output$table2 = DT::renderDataTable({
                   req(input$Search)
               df2()}, options = list(dom = 't'))

      df3 <- reactive({
         if (input$Search != "") {     
               sqldf(paste0("SELECT  *  
                    FROM df WHERE  empName  LIKE '%",input$Search,"%'"))  
           }})                

output$table3 = DT::renderDataTable({ 
            req(input$Search)
              df3()}, options = list(dom = 't'))


         df4 <- reactive ({
               SelectedNames <-stringr::str_c(stringr::str_c("'", input$Search, "'"), collapse = ',') 

      sqldf(paste0("SELECT  empAge, empSalary  
             FROM df  WHERE  empName IN (",SelectedNames,")  "))
                })     

 output$table4 = DT::renderDataTable({ 
              req(input$Search)
             df4()}, options = list(dom = 't')) 
           })

I am working with MySQL queries. In Table 1 the data displayed is the whole employee dataframe , I cannot do that for thousands of rows from the original data. The Selectize choices are from a dataframe of UNIQUE values of two columns empName and empID

In table 2 I select the employee names from selectizeInput but display the corresponding IDs .

In Table 3, it only shows one ID value corresponding to selected name value from the selectizeInput.

In table 4, the code allows to query other details from multiple selection of the selectizeInput. In this case I pass the multiple selections and use stringr to convert to character strings and pass the variable to the MySQL query.

What I am looking for is to be able to select multiple names from selectizeInput but pass the corresponding multiple employee IDs to the MySQL query to get the results like in Table 4.

Thus basically combine the ability to select names but pass the values of the Id column to allow multiple select in a query.

Any advice or direction towards solving this will be appreciated.

Upvotes: 1

Views: 216

Answers (1)

YBS
YBS

Reputation: 21349

Choose empNames in selectizeInput to get empID in df2() - already done this way in your code. Then use this empID on the original dataframe df to obtain df4 as shown below. Then you get the desired table4. This is what I understood as your expectation.

df4 <- reactive({
      df4 <- df %>% subset(empID %in% df2()$empID) %>% select(empID, empAge, empSalary)
    })

Upvotes: 1

Related Questions