I'm build a shiny app.
I need to query database based on user input. I guess I need to glue nothing into sql when user keep the default input values unchanged and glue something into the sql when user modify the default input values.
library(glue)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris)
empty <- NULL
value <- as.symbol("ORDER BY sepal_width")
glue_sql("SELECT * FROM iris {empty}", .con = con)
#> <SQL>
glue_sql("SELECT * FROM iris {value}", .con = con)
#> Warning in is.na(res): is.na() applied to non-(list or vector) of type
#> 'symbol'
#> <SQL> SELECT * FROM iris ORDER BY sepal_width
Created on 2019-04-12 by the reprex package (v0.2.1)
Just find glue DBI::SQL('') into the statement helps me. Not sure is it desinged to work like this or it is just a coincidence. So I create a issue in glue repo .
opened 03:31AM - 13 Apr 19 UTC
closed 03:56PM - 03 Apr 20 UTC
I'm trying to build dynamic sql based on shiny app input. i.e., when user change… the default selected values, glue a sql expression, and glue nothing when user do not modified the default values.
I find glue `DBI::SQL('')` into a sql statement can help me. @jimhester Is it designed that a `DBI::SQL()` object could be glue into a sql statment?
By the way, it seems `.na` argument doesn't work.
``` r
library(DBI)
library(glue)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris)))
str(iris)
#> 'data.frame': 150 obs. of 5 variables:
#> $ sepal_length: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
#> $ sepal_width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
#> $ petal_length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
#> $ petal_width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
#> $ species : Factor w/ 3 levels "setosa","versicolor",..: 1 1 1 1 1 1 1 1 1 1 ...
DBI::dbWriteTable(con, "iris", iris)
default_value <- c("setosa", "versicolor", "virginica" )
user_input_default <- c("setosa", "versicolor", "virginica" )
# keep default unchanged
if (all(sort(default_value) == sort(user_input_default))) {
sql_expr <- DBI::SQL('')
} else {
sql_expr <- glue_sql("WHERE species IN ({user_input_default*})", .con = con)
}
sql_stat <- glue_sql("SELECT * FROM iris {sql_expr} limit 3", .con = con)
dbGetQuery(con, sql_stat)
#> sepal_length sepal_width petal_length petal_width species
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3.0 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
# user changed default values
user_input_changed <- c("setosa", "versicolor" )
if (all(sort(default_value) == sort(user_input_changed))) {
sql_expr <- DBI::SQL('')
} else {
sql_expr <- glue_sql("WHERE species IN ({user_input_changed*})", .con = con)
}
#> Warning in sort(default_value) == sort(user_input_changed): longer object
#> length is not a multiple of shorter object length
sql_stat <- glue_sql("SELECT * FROM iris {sql_expr} limit 3", .con = con)
dbGetQuery(con, sql_stat)
#> sepal_length sepal_width petal_length petal_width species
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3.0 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
# it seeems `.na` argument doesn't work
glue_sql("select * from isis where species sepal_length > {NA}", .con = con, .na = 3)
#> <SQL> select * from isis where species sepal_length > NULL
```
<sup>Created on 2019-04-13 by the [reprex package](https://reprex.tidyverse.org) (v0.2.1)</sup>
1 Like
system
May 4, 2019, 3:35am
3
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.