dbGetQuery with multiple parameters

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)