Creating a SQL query using selectinput in R shiny

Question

I am trying to create a dynamic query using select input.

Something like

Select * from basket where fruits in("apple","banana","cherry")

I have a table called fruit_list which populates my selectinput box.

selectInput("fruit_list", label = h5("Select fruit"), multiple = T, choices = (dbGetQuery(conn, "SELECT fruit from fruit_list');")))

So far, when i renderprint my selection I get "apple" "banana" "cherry" I need a comma between the elements to get "apple","banana","cherry" When i choose a single element from the multiselect box "apple"

Select * from basket where fruits in("apple")

my application runs perfectly. However, when i select more than one element "apple" "banana" I get an error: Expecting a single string value: [type=character; extent=2].

I apologize for not being as explicit in the beginning

The paste command can be used to concatenate an SQL query string together — cory
– cory, Commented Sep 23, 2019 at 16:24

G. Grothendieck · Accepted Answer · 2019-09-23 16:38:17Z

1

Suppose ans is a non-empty subset of c("a", "b", "c", "d") . For example,

ans <- c("b", "d")
sprintf("select * from table where item in (%s)", toString(shQuote(ans, "csh")))

giving:

[1] "select * from table where item in ('b', 'd')"

If you are using R 3.6 or later you can optionally replace the shQuote(ans, "csh") with sQuote(ans, FALSE) .

If a, b, c, d in the question were supposed to represent numbers then we don't need the quotes so we can replace toString(...) with just toString(ans) .

No packages are used.

edited Sep 23, 2019 at 16:38

answered Sep 23, 2019 at 16:29

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yifu Yan · Accepted Answer · 2019-09-23 16:25:52Z

0

selectInput takes in user input as a character vector, glue should be more easier to use when your query is more complex.

library(glue)
# user_input <- input$selectInput # e.g. c("a","b","c","d")
user_input <- c("a","b","c","d")
sql_where <- paste0(user_input,collapse = ", ")
glue("select * from table where item in ({sql_where})")

It translates into:

select * from table where item in (a, b, c, d)

answered Sep 23, 2019 at 16:25

Yifu Yan

6,1561 gold badge21 silver badges37 bronze badges

Collectives™ on Stack Overflow

Creating a SQL query using selectinput in R shiny

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related