sql - RSQLite:在同一选择查询中绑定(bind)集和标量

Question

如何在 dbGetQuery() 的 bind.data 参数中为像

这样的 SQL 语句传递标量和一组值

select * from tst where x = ? and y in (?)

这是我尝试过的:

> library("RSQLite")
> c <- dbConnect (SQLite())
> dbGetQuery(c, "create table tst (x int, y int)")
> dbGetQuery(c, "insert into tst values (?, ?)", data.frame(x=c (1,2,1,2), y=c(3, 4, 5, 6)))
> dbReadTable(c, "tst")
  x y
1 1 3
2 2 4
3 1 5
4 2 6
> dbGetQuery (c, "select * from tst where x = ? and y not in (?)", data.frame(x=2, y=I (list(7,6))))
Error in sqliteFetch(rs, n = -1, ...) : 
  RAW() can only be applied to a 'raw', not a 'double'

从阅读源代码来看，任何非 data.frame bind.data 参数都被强制通过 as.data.frame()，所以我想这没什么意义尝试除数据框以外的任何东西。

注意:哎呀，似乎即使绑定(bind)一个集合也是有问题的:

> dbGetQuery(c, "select * from tst where y not in (?)", c(7,6))
  x y
1 1 3
2 2 4
3 1 5
4 2 6
5 1 3
6 2 4
7 1 5

这清楚地表明从 R 发送了 2 个单独的查询(其中一个返回 4 个结果，另一个返回 3 个结果)； SQLite 永远不会看到设置参数。

早期说明:我希望数据库引擎过滤适当的行，我不希望 R 计算笛卡尔积。在上面的示例中，简单地去掉 I() 会创建一个 2 行的数据框(感谢 R 的回收)，其中之一就是解决方案。 R 将这两行中的每一行发送到 sqlite，当然第二行匹配。但是下面显示了 SQLite 引擎实际上并没有接收到带有常规 data.frames 的设置参数:

> dbGetQuery(c, "select * from tst where x in (?) and y in (?)", data.frame(x=c(3,2), y=c(6,7)))
[1] x y
<0 rows> (or 0-length row.names)
> dbGetQuery(c, "select * from tst where x in (?) and y in (?)", data.frame(x=c(3,2), y=c(7,6)))
  x y
1 2 6

Answer 1

为什么指定 y = I(list(7,6)) 而不是 y=c(6,7)？这似乎有效:

dbGetQuery (c, 
            "select * from tst where x = ? and y in (?)", 
            data.frame(x=1, y=c(7,6)))

您可能正在寻找 expand.grid。

dbGetQuery (c, 
            "select * from tst where x = ? and y in (?)", 
            expand.grid(x=c(2,3), y=c(7,6)))

编辑:另一种选择(并不漂亮)是替换 R 中的 ?。类似于以下内容:

dbGetQuerySet <- function(con, statement, ...){
  if (length(list(...)) > 0){
    bind.data <- list(...)[[1]]
    for (set in as.data.frame(bind.data)){  
      statement <- sub('\\?', paste(set, collapse=","), statement)
    }
  }
  sqliteQuickSQL(con, statement, ...)
}