SQL syntax issues using glue and selectInput multiple

[danimaiams]

How can i allow the Query to search for many values using "selectInput" in the "GLUE Package"?

I have the following example... but it just did not work:

library(shiny)
library(glue)

ui <- fluidPage(
    
    selectInput("ordemlista", "Tipo de Ordem:",
                       choices=list('LQ','LQR', 'LQX'),
                       multiple = TRUE
    ),
    
    tableOutput(outputId = "query")
)

server <- function(input, output) {
    output$query <- renderText({
        glue ("
             select cod_ordem_producao
             from pla.cod_ordem_producao
             WHERE cod_ordem_producao IN '{input$ordemlista}' 
              ")
    })
}

shinyApp(ui = ui, server = server)

[jlacko]

Your SQL syntax of the IN clause is not right; you need to have the values in brackets.

I believe this will work better, though I can not really reproduce your code

server <- function(input, output) {
    output$query <- renderText({
        glue ("
             select cod_ordem_producao
             from pla.cod_ordem_producao
             WHERE cod_ordem_producao IN ({input$ordemlista}) 
              ")
    })

When debugging the where clause should look somewhat like WHERE cod_ordem_producao IN ('LQ', 'LQR') or what not - individual values in single quotes, separated by commas, the whole set in brackets.

[danimaiams]

So:

When I tried
WHERE cod_ordem_producao IN ({input$ordemlista})
It generate an error.

When I tried
WHERE cod_ordem_producao IN ('{input$ordemlista}')
It works with ONLY 1 selected input. But, when i select 2 itens, it calls an error.

[jlacko]

The problem with the multiple values should be overcome by collapsing the values to a single string; try this:

WHERE cod_ordem_producao IN ('{paste(input$ordemlista, "', '")}')

You may have to escape some of the quotation marks.

[andresrcs]

Just to exemplify Jindra's solution, this seems to produce the desired output

output$query <- renderText({
        glue ("
             select cod_ordem_producao
             from pla.cod_ordem_producao
             WHERE cod_ordem_producao IN ('{paste(input$ordemlista, collapse = '\\', \\'')}') 
              ")
    })

[jlacko]

Thanks @andresrcs! I did not have a working Shiny on hand when I was composing the reply - but by looking at your SQL code: yes, this is what I had in mind

[danimaiams]

I just did not understand why, but the solution given by our friend @andresrcs worked.

The:
('{paste(input$ordemlista, collapse = '\\', \\'')}')
Really works.

I'm trying to understand what it means, but, very thanks both of you, guys!

[jlacko]

The solution worked, because it managed to collapse the vector of selected ordemlistas from a vector to a single string value; that is what the paste() is there for.

You needed a string that would start with an opening bracket and a single quote, contain all the ordemlistas values in single quotes and separated by commas, and then end with another single quote and a closing bracket.

The slashes are escape character, as you need a trick to use single quotes within the query string (normally a single quote could signal the end of the string).

[andresrcs]

@jlacko

The solution worked, because it managed to collapse the vector of selected ordemlistas from a vector to a single string value; that is what the paste() is there for.

Some ilustrations for this part

input <- data.frame(ordemlista= c("a", "b", "c"), 
                    stringsAsFactors=FALSE)
# This is a character vector of length 3
input$ordemlista
#> [1] "a" "b" "c"
length(input$ordemlista)
#> [1] 3

# This is a string or a character vector of lenght 1
string <- paste(input$ordemlista, collapse = "', '")
string
#> [1] "a', 'b', 'c"
length(string)
#> [1] 1