英文:
dbGetQuery with multiple parameters
问题
我有一个问题。我想将**"querys1"作为参数传递给dbGetQuery**，但我无法获取我感兴趣的数据。然而，当我使用**"querys2"和"Filter2"运行dbGetQuery**时，我可以获取我的数据。
如何同时传递这两个Where子句？
感谢您的帮助。
英文:
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$stateFilter2 <- "Luxury" #input$homequery1 <-paste0(paste0("$", seq_along(Filter1)), collapse = ", ")query2 <-paste0(paste0("$", seq_along(Filter2)), collapse = ", ")querys1 <- paste0("SELECT *FROM tableWHERE state IN (",query1,") AND home IN (",query2,")")querys2 <- paste0("SELECT *FROM tableWHERE home IN (",query2,")")data1 <- dbGetQuery(conn, querys2, as.list(Filter2))
How can I pass both Where clauses ?
Thank for your help.
答案1
得分: 2
问题在于您正在对每个过滤器进行序列化，而不是对所有过滤器进行序列化。我们可以看到这个问题在下面的代码中：
querys1# [1] "SELECT *\n FROM table\n WHERE state IN ($1) AND home IN ($1)"
也就是说，这两个集合都是 $1。这意味着 state 和 home 被与相同的文字值进行比较，而不是每个向量的第一个值。$-数字需要在所有参数上进行序列化。
对于每个 Filter，长度为1，似乎很简单，但我推测它们可以是 "1个或多个"，所以我们需要做更多的工作。
query1 <-paste0(paste0(""$"", seq_along(Filter1)), collapse = "", ")query2 <-paste0(paste0(""$"", length(Filter1) + seq_along(Filter2)), collapse = "", ")querys1 <- paste0(""SELECT *\n FROM table\n WHERE state IN ("",query1,"") AND home IN ("",query2,"")")querys2 <- paste0(""SELECT *\n FROM table\n WHERE home IN ("",query2,"")"")
现在我们看到
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)"
请注意，一旦您将 $ 嵌入查询中，您就必须包括与列表一样长的参数。因此，即使我们可能不使用 $1 参数，我们仍然需要在 params= 中包括它。
然后查询将如下所示：
data1 <- dbGetQuery(conn, querys1, as.list(c(Filter1, Filter2)))data2 <- dbGetQuery(conn, querys2, as.list(c(Filter1, Filter2)))
如果您希望在 querys2 检索中不包括 Filter1，那么您需要重新制定 $-计数，不考虑 Filter1。
另一种可以更自由地控制参数和 $ 引用的替代方法是使用一个辅助函数，将非计数占位符（如 ?，许多其他DBMS用于参数绑定的占位符）转换为 $ 计数，然后传递查询和参数列表，它将用 $ 计数替换 ? 文字。
我将稍微扩展这个函数，以处理 "IN" 1或多个向量。
# @param query 长度为1的字符，带有 "?name" 命名绑定位置# @param params 命名参数列表，名称对应于 "?name" 绑定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)}
它的使用应该很直观：
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"
这将用作：
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)
英文:
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 tableWHERE state IN (",query1,") AND home IN (",query2,")")querys2 <- paste0("SELECT *FROM tableWHERE 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" bindsxlate <- 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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论