mysql - 使用更少的代码将 Shiny 的小部件和 MySQL 查询的输入结合起来

Question

我有一个应用程序，允许用户在 Shiny 的应用程序上使用输入小部件查询 MySQL 数据库。查询也涉及连接表。当使用 IF ...ELSE 语句来确定小部件是否为空或是否有一些用户输入时，代码变得太长，如下面的代码所示。

示例 MySQL 数据可以创建如下:

   CREATE TABLE  quoteauthors (
     FirstName VARCHAR(255) ,
     LastName VARCHAR(255) ,
      authorID VARCHAR(255) 
    );

 CREATE TABLE  quotes (
   quote VARCHAR(255) ,    
    authorID VARCHAR(255) 
    );

 INSERT INTO quoteauthors
    VALUES ('Albert', 'Einstein', 'a1'),
           ('Stephen', 'Hawking', 'a2'),
           ('Isaac', 'Newton', 'a3');

  INSERT INTO quotes
     VALUES ('Unthinking respect for authority is the greatest enemy of truth.', 'a1'),
        ('In the middle of difficulty lies opportunity.', 'a1'),
        ('Intelligence is the ability to adapt to change.', 'a2'),
        ('Science is not only a disciple of reason but, also, one of romance and passion.', 'a2'),
        ('If I have seen further it is by standing on the shoulders of Giants.', 'a3'),
        ('I can calculate the motion of heavenly bodies but not the madness of people', 'a3');

Shiny 应用示例如下:

library(shiny)
library(shinydashboard)
library(DBI)
library(RMySQL)

ui <- dashboardPage(
  dashboardHeader(),
  dashboardSidebar(
    sidebarMenu(
      menuItem("QUOTE Search", tabName = "Tabs", icon = icon("object-ungroup"))

    )
  ),
  dashboardBody(
    tabItem(tabName = "Tabs",
            fluidRow(
              column(width=3, 
                     box(
                       title="Search ",
                       solidHeader=TRUE,
                       collapsible=TRUE,
                       width=NULL,
                       textInput("quoteSearch1", " Search Term 1 ",  '', placeholder = "Type search term"),
                       radioButtons("combi", "Logical Operator to Combine  Terms:",
                                    c(
                                      "AND" = "AND",
                                      "OR" = "OR" 

                                    ), inline = TRUE),
                       textInput("quoteSearch2", " Search Term 2 ",  '', placeholder = "Type search term"),

                       selectInput("authorchoice", "Select AUTHOR", selected = NULL, multiple = T,
                                   choices=c('Albert','Stephen','Isaac')),
                       submitButton("Search")
                     )
              ),

              column( width=9,
                      tabBox(
                        width="100%",
                        tabPanel("Search Results", 
                                 htmlOutput("quotesearchdetails")
                        )))))))

server <- function(input, output) {


  output$quotesearchdetails <-renderUI({

    if(input$quoteSearch1!=""){
      con <- dbConnect(MySQL(), 
                       user='XXXXXXXXXXX', 
                       port = 3306, password='XXXXXXXXXXX', 
                       dbname='XXXXXXXXXXX', 
                       host='XXXXXXXXXXX')
                      dbSendQuery(con, "SET NAMES utf8mb4;")
                      dbSendQuery(con, "SET CHARACTER SET utf8mb4;")
                      dbSendQuery(con, "SET character_set_connection=utf8mb4;")
                      on.exit(dbDisconnect(con), add = TRUE) 

      quotedetails <- reactive({

        if (input$authorchoice == ""){
          if (input$quoteSearch2 == ""){
              dbGetQuery(con, statement = 
                       paste0(" SELECT q.quote, a.FirstName, a.LastName 
                                  FROM quotes q
                                   JOIN quoteauthors  a
                                     ON (q.authorID = a.authorID)
                                       WHERE (q.quote LIKE '%",input$quoteSearch1,"%')  "))                    

        }else{
          if (input$combi == "AND"){
            dbGetQuery(con, statement = 
                        paste0("
                               SELECT q.quote, a.FirstName, a.LastName 
                                FROM quotes q
                                 JOIN quoteauthors  a
                                   ON (q.authorID = a.authorID)
                                     WHERE (q.quote LIKE '%",input$quoteSearch1,"%' AND
q.quote LIKE '%",input$quoteSearch2,"%')"))


          }else{
            dbGetQuery(con, statement = 
                         paste0("
                                SELECT q.quote, a.FirstName, a.LastName 
                                 FROM quotes q
                                  JOIN quoteauthors  a
                                   ON (q.authorID = a.authorID)
                                    WHERE (q.quote LIKE '%",input$quoteSearch1,"%' 
                                 OR q.quote LIKE '%",input$quoteSearch2,"%')"))

          }                               

        }

        }else{
          if (input$quoteSearch2 == ""){
            dbGetQuery(con, statement = 
                         paste0("
                                SELECT q.quote, a.FirstName, a.LastName 
                                 FROM quotes q
                                  JOIN quoteauthors  a
                                   ON (q.authorID = a.authorID)
                                     WHERE (q.quote LIKE 
                                       '%",input$quoteSearch1,"%' 
                                       AND a.FirstName LIKE '%",input$authorchoice,"%') "))                                

          }else {
            if (input$combi == "AND"){
              dbGetQuery(con, statement = 
                           paste0("
                                  SELECT q.quote, a.FirstName, a.LastName 
                                   FROM quotes q
                                    JOIN quoteauthors  a
                                     ON (q.authorID = a.authorID)
                                      WHERE (q.quote LIKE '%",input$quoteSearch1,"%' AND
                                  q.quote LIKE '%",input$quoteSearch2,"%') AND
                                  a.FirstName LIKE '%",input$authorchoice,"%' "))

            }else{
              dbGetQuery(con, statement = 
                           paste0("
                                  SELECT q.quote, a.FirstName, a.LastName 
                                    FROM quotes q
                                     JOIN quoteauthors  a
                                       ON (q.authorID = a.authorID)
                                         WHERE (q.quote LIKE '%",input$quoteSearch1,"%' OR
                                  q.quote LIKE '%",input$quoteSearch2,"%')
                                  AND
                                  a.FirstName LIKE '%",input$authorchoice,"%' "))

            }
          }
      }

      })

      outputed=""
      quotedetailsreturned <- quotedetails()
      if (dim(quotedetailsreturned)[1] > 0){
        for(i in seq(from=1,to=dim(quotedetailsreturned)[1])){ 

          outputed<-paste(outputed,
                          paste("Author's First name: ",quotedetailsreturned[i,"FirstName"]),
                          sep="<br/><br/>")
          outputed<-paste(outputed,
                          paste("Author's Last name: ",quotedetailsreturned[i,"LastName"]),
                          sep="<br/><br/>")
          outputed<-paste(outputed,
                          paste("Quote: ",quotedetailsreturned[i,"quote"]),
                          sep="<br/><br/>")

        }

      } else { outputed <-"your search yielded no results."}

      HTML(outputed)
    }else {
      paste("Please input a search term at least in the first field")
    }


  })


}
shinyApp(ui, server)

我正在寻找一种解决方案，了解如何在我的代码中使用 IF...ELSE 语句来避免重复和长代码。我可以使用哪些最佳编程实践将 MySQL 查询与用户在各种 Shiny 小部件上的输入结合起来，包括 textInput、radioButtons、selectize/selectInput 等等，考虑到一些输入可以留空，因此不应在查询中考虑。

Answer 1

我会首先只构建查询字符串，一步一步，根据所选设置逐步添加每个子句。构建完成后，执行查询。使代码更短且更易于阅读。