I have a problem. I would like to pass "querys1" as parameters in dbGetQuery but I can't get the data I'm interested in. However, I can get my data when I run dbGetQuery with "querys2" and "Filter2".
Filter1 <- "State" #input$state
Filter2 <- "Luxury" #input$home
query1 <-
paste0(paste0("$", seq_along(Filter1)), collapse = ", ")
query2 <-
paste0(paste0("$", seq_along(Filter2)), collapse = ", ")
querys1 <- paste0("SELECT *
FROM table
WHERE state IN (",
query1,
") AND home IN (",
query2,
")")
querys2 <- paste0("SELECT *
FROM table
WHERE home IN (",
query2,
")")
data1 <- dbGetQuery(conn, querys2, as.list(Filter2))
How can I pass both Where clauses ?
Thank for your help.
The problem is that you are sequencing along each of your filters instead of all of your filters. We can see why this is a problem with:
querys1
# [1] "SELECT *\n FROM table\n WHERE state IN ($1) AND home IN ($1)"
(That is, both sets are $1.) What this means is that state and home are compared against the same literal value, not the first of each vector. The $-numbers need to be sequencing along all parameters combined.
With length-1 for each of your Filters, it seems trivial, but I'm inferring they can be "1 or more", so we need to do a little more work.
query1 <-
paste0(paste0("$", seq_along(Filter1)), collapse = ", ")
query2 <-
paste0(paste0("$", length(Filter1) + seq_along(Filter2)), collapse = ", ")
querys1 <- paste0("SELECT *
FROM table
WHERE state IN (",
query1,
") AND home IN (",
query2,
")")
querys2 <- paste0("SELECT *
FROM table
WHERE home IN (",
query2,
")")
Now we see
querys1
# [1] "SELECT *\n FROM table\n WHERE state IN ($1) AND home IN ($2)"
querys2
# [1] "SELECT *\n FROM table\n WHERE home IN ($2)"
Recognize that once you embed $ in a query, you are beholden to having parameters of a list that long. So even though we may not be using the $1 parameter, we still need params= to include it.
The queries would then be:
data1 <- dbGetQuery(conn, querys1, as.list(c(Filter1, Filter2)))
data2 <- dbGetQuery(conn, querys2, as.list(c(Filter1, Filter2)))
If you prefer to not include Filter1 in your querys2 retrieval, then you need to reform the $-counting without Filter1 consideration.
One alternative to this where you can be a little looser in controlling the parameters and $-referencing is to use a helper function that converts a non-counting placeholder (such as ?, used by many other DBMSes for parameter binding), then pass the query and list of params, and it'll replace the ? literals with the $-counts.
I'll expand this a little to handle "IN" 1-or-more vectors.
#' @param query length-1 character, with "?name" named binding locations
#' @param params named list of params, names correspond to "?name" binds
xlate <- function(query, params) {
stopifnot(length(query) == 1L)
gre <- gregexpr("\\?[A-Za-z]+", query)
nms <- sub("^\\?", "", regmatches(query, gre)[[1]])
newparams <- params[nms]
leng <- lengths(newparams)
leng <- c(0, leng[-length(leng)])
qmarks <- mapply(function(pre, vec) {
paste(paste0("$", pre + seq_along(vec)), collapse = ",")
}, leng, newparams)
newparams <- unname(unlist(newparams))
regmatches(query, gre) <- list(qmarks)
list(query = query, params = newparams)
}
And its use should be intuitive:
xlate("select * from table where state in (?st) or home in (?hm)", list(st="QQ", hm=c("abc","quux")))
# $query
# [1] "select * from table where state in ($1) or home in ($2,$3)"
# $params
# [1] "QQ" "abc" "quux"
xlate("select * from table where home in (?hm)", list(st="QQ", hm=c("abc","quux")))
# $query
# [1] "select * from table where home in ($1,$2)"
# $params
# [1] "abc" "quux"
xlate("select * from table where state in (?hm) or home in (?hm)", list(st="QQ", hm=c("abc","quux")))
# $query
# [1] "select * from table where state in ($1,$2) or home in ($3,$4)"
# $params
# [1] "abc" "quux" "abc" "quux"
This would be used as:
tmp <- xlate("select * from table where state in (?hm) or home in (?hm)", list(st="QQ", hm=c("abc","quux")))
res <- dbGetQuery(con, tmp$query, params = tmp$params)