mysql - mysql 中的空 where 子句(当我使用 RMySql dbGetQuery 时)

Question

我想做一些与这些非常相似的事情:

When mysql WHERE clause is empty, return all rows

Possible to have PHP MYSQL query ignore empty variable in WHERE clause?

如果我要子集化的变量是NULL，我希望忽略 where 子句。但是，我正在使用 dbGetQuery 从 R 访问我的 MySQL 数据库。到目前为止，我有这样的代码

write_pid_clause = function(p_id=NULL){
 if(is.null(p_id)){return(NULL)}
 else {return(paste0("where project_id = ",p_id)  )}
}

如果未指定p_id，它将写入正确的where语句行为，以及代码:

  dbGetQuery( con,paste0("select MIN(completion_date) from run ",write_pid_clause(p_id))))

这工作正常，但是，如果我想在 where 子句中插入更多条件，例如，我会遇到困难。如果我想在 p_id = NULL 时添加条件 and status = 'complete'。

有人对我如何在 R 中优雅地做到这一点有任何好主意吗？

编辑这里还有一些代码来演示我想要做什么，这是关于连接后的 and 子句(执行 where 有点棘手)

make_and_clauses = function(p_id = "",start_date="", end_date=""){

 conditions = list(
   list(" and r.project_id ='", p_id,"'"),
   list(" and r.completion_date >= '",start_date,"'"),
   list(" and r.completion_date <= '", end_date, "'"))
 condition_values = c(p_id,start_date, end_date)
 conditions[which(condition_values =="")] <- ""
 conditions = unlist(conditions,recursive=TRUE)
 paste0(conditions,collapse="")

}

给出输出

> make_and_clauses(2,3,4)
[1] " and r.project_id ='2' and r.completion_date >= '3' and r.completion_date <= '4'"
> make_and_clauses(2,,4)
[1] " and r.project_id ='2' and r.completion_date <= '4'"
> make_and_clauses(,3,2)
[1] " and r.completion_date >= '3' and r.completion_date <= '2'"

>

Answer 1

从你的功能开始，我建议如下:

make_and_clauses <- function(p_id, start_date, end_date) {
    conditions = c(
        if (!missing(p_id)) sprintf("r.project_id ='%i'", p_id),
        if (!missing(start_date)) sprintf("r.completion_date >= '%i'", start_date),
        if (!missing(end_date)) sprintf("r.completion_date <= '%i'", end_date)
    )
    paste(conditions, collapse=" and ")
}

返回:

make_and_clauses(2,3,4)
# [1] "r.project_id ='2' and r.completion_date >= '3' and r.completion_date <= '4'"
make_and_clauses(2,,4)
# [1] "r.project_id ='2' and r.completion_date <= '4'"
make_and_clauses(,3,2)
# [1] "r.completion_date >= '3' and r.completion_date <= '2'"
make_and_clauses(,3,)
# [1] "r.completion_date >= '3'"

没有任何争论会导致什么，这一点很重要:

make_and_clauses(,,)
# [1] ""

因此可以编写 WHERE 子句:

make_and_clauses <- function(p_id, start_date, end_date) {
    conditions = c(
        if (!missing(p_id)) sprintf("r.project_id ='%i'", p_id),
        if (!missing(start_date)) sprintf("r.completion_date >= '%i'", start_date),
        if (!missing(end_date)) sprintf("r.completion_date <= '%i'", end_date)
    )
    if (length(conditions)>0) paste("WHERE", paste(conditions, collapse=" and ")) else ""
}

并将其用作

paste("select MIN(completion_date) from run", make_and_clauses(,3,2))
# [1] "select MIN(completion_date) from run WHERE r.completion_date >= '3' and r.completion_date <= '2'"
paste("select MIN(completion_date) from run", make_and_clauses())
# [1] "select MIN(completion_date) from run "