R query dynamic where with shiny input

Question

I query data from R shiny app

output$ceb_selector <- renderUI({
    selectInput("select_tab", label = ("ceb"),
                choices = list("xx" , "yy", "All"),
                selected = "All")

This is my old sql

    t1 <- dbGetQuery(pool,statement = paste0("select    ",input$date," , name ,
                          sum(",input$x1,"*y) as ",input$x1," ,
                          sum(gg) as ggsum
                          from table_name 
                          where name = '",input$name,"'
                          and ",input$date," between '",input$date_range[1],"' and '",input$date_range[2],"'
                          group by ",input$date," ,name ")  )  
write.table(t1, file="emp.csv", sep = ",", row.names=FALSE)

I want to add where condition from input$ceb that have 3 choices = xx, yy, all

But in database column ceb only have data xx and yy don't have all (I want use 'all' to select both xx and yy)

table

inputshiny

What I thought is If input = xx will where ceb = xx

If input = yy will where ceb = yy

If input = All will where xx and yy

I tried

paste0("select        ",input$date," , name ,
                      sum(",input$x1,"*y) as ",input$x1," ,
                      sum(gg) as ggsum
                      from table_name 
                      where name = '",input$name,"'
                      and ",input$date," between '",input$date_range[1],"' and '",input$date_range[2],"'
                      and ceb =
                      case 
                               when  ",input$ceb," = xx then 'xx'
                               when  ",input$ceb," = yy then 'yy' 
                      end 
                      group by ",input$date," ,name ")

AND

case 
   when  ",input$ceb," = xx then and tab = 'xx'
   when  ",input$ceb," = yy then and tab = 'yy' 
end

It's still error.

Ugh, you should really use DBI::dbQuoteString(con, ...) for values, and perhaps DBI::dbQuoteIdentifier for field/column names. (Otherwise, think sql-injection or just really-hard-to-troubleshoot problems.) — r2evans
– r2evans, Commented Jan 13, 2020 at 5:12
I see nothing different between your 'xx' and 'yy' cases, should there be something different going on there? — r2evans
– r2evans, Commented Jan 13, 2020 at 5:15
I have used dbGetQuery ,I query and then create table from query. Main Problem is case 'All' from input because in datatable this column do not have 'All'. *What I thought is If input = xx will where ceb = xx , If input = yy will where ceb = yy , If input = All will where xx and yy * — Sup'A
– Sup'A, Commented Jan 13, 2020 at 6:16
Perhaps: if the input is not "All", then you add and ceb in ('xx','yy'); and if the input is "All", then you don't concatenate that string. I don't know, really, I don't know enough about the inputs or the table. — r2evans
– r2evans, Commented Jan 13, 2020 at 6:22
While I realize that you are getting input$... from shiny, this really has nothing to do with shiny in its core, is that right? BTW: an image of a data table (especially a single-column one that does not clarify things) is discouraged: it cannot be searched and breaks screen-readers. Consider adding "real data" in various forms, either from dput(...), data.frame(...), or (least-preferred) copy output from the console; this last one is problematic in that there are lots of possible ambiguities that we cannot see well on the console. — r2evans
– r2evans, Commented Jan 13, 2020 at 6:40

r2evans · Accepted Answer · 2020-01-13 07:04:02Z

3

I'm not going to be able to reproduce the full complexity of your table and shiny app, but here's a simplified local example where I think there are two methods.

I'm using sqldf on a local frame to mimic your postgres database; I don't think the specific DBMS matters in this case.

table_name <- data.frame(
  row = 1:4,
  ceb = c('xx', 'yy', 'xx', 'yy')
)

Query formed conditionally

input_ceb <- "xx"
qry <- "select * from table_name where row > 0"
qry2 <- if (input_ceb != "All") paste("and ceb = ", sQuote(input_ceb))
sqldf::sqldf(paste(qry, qry2))
#   row ceb
# 1   1  xx
# 2   3  xx

input_ceb <- "All"
qry <- "select * from table_name where row > 0"
qry2 <- if (input_ceb != "All") paste("and ceb = ", sQuote(input_ceb))
sqldf::sqldf(paste(qry, qry2))
#   row ceb
# 1   1  xx
# 2   2  yy
# 3   3  xx
# 4   4  yy

If you look at qry2 now, it's NULL, so the query will not contain the and ceb= component. (Note that you should make sure that the query before and after this conditionally-included portion can work equally with it present or absent.)

Query including more conditional

(Still using input_ceb as above.) This causes the DBMS to take into consideration if ceb is "All", short-circuiting the second half. I don't know that this is any better, but it works the same.

qry <- paste(
  "select * from table_name where row > 0 and ('All' = ", sQuote(input_ceb),
  "or ceb = ", sQuote(input_ceb), ")"
)
sqldf::sqldf(qry)

Bottom line, use whichever one makes more sense to you and/or incorporates well into the rest of your query generation.

Notes:

I use sQuote here because I'm not using DBI, though like my comment on your question, you should consider being a little more defensive in your coding: use DBI::dbQuoteString in place of sQuote here. The DBMS may not like wrapping numbers in ticks, though, so use smartly.
the only reason I included row > 0 was so that I had a where clause to which I could just add and ceb = '...'; without row > 0, there would be no where and therefore and would be a syntax violation. You won't need it, as I believe you have where clauses that are always present.

you might find sprintf or glue::glue to be slightly better for interpolating strings in there; for instance, these are equivalent to the second example above:

sprintf(
  "select * from table_name where row > 0
   and ('All' = %s or ceb = %s)", sQuote(input_ceb), sQuote(input_ceb)
)
glue::glue(
  "select * from table_name where row > 0
   and ('All' = {sQuote(input_ceb)} or ceb = {sQuote(input_ceb)})"
)

answered Jan 13, 2020 at 7:04

r2evans

167k8 gold badges92 silver badges176 bronze badges

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

4 Comments

Sup'A Over a year ago

I have create qry1-3 ,but I still confused about syntax . t1 <- dbGetQuery(pool,statement = paste0(qry1, qry2,qry3) ) What's it should be.

r2evans Over a year ago

Does that not work? Are you getting warnings or errors? I don't know, it's still rather unclear with your tables/query.

Sup'A Over a year ago

Could not create execute: Warning: Error in anytime_cpp: Unsupported Type

qry1 <- " \" select ',input$sel_date_type,' , name,           sum(qty) as qty           from tablename           where name = ',input$name,'           and ',input$date,' between ',input$date_range[1],' and ',input$date_range[2],'   "

qry2 <- if(',input$ceb,' != 'All') paste(" and ceb = " ,input$ceb ) qry3 <- " group by ',input$date,' ,product_internal_name \" "

r2evans Over a year ago

I suspect your code is mixing single and double quotes, as you have at least one R syntax error and some SQL syntax errors in that as well.

Collectives™ on Stack Overflow

R query dynamic where with shiny input

1 Answer 1

Query formed conditionally

Query including more conditional

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Query formed conditionally

Query including more conditional

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related