sql - 使用 sequelize 和 node.js 生成将忽略 where 子句的 SQL 查询

Question

我使用 node.js 作为后端语言和 Microsoft SQL 作为数据库。我正在使用 sequelize ( http://docs.sequelizejs.com ) 来查询 SQL。下面是我的 Web API 路由处理程序代码:

const sequelize = require('sequelize');

module.exports = {
getinfo(req, res) {
  let {search, id, fromDate, toDate, firstName, lastName} = req.body;
  const condition = "";

  if(search != null && search != '')
  {
      condition += "id LIKE '%" + search + "%'";
      condition += "fromDate LIKE '%" + search + "%'";
      condition += "toDate LIKE '%" + search + "%'";
      condition += "firstName LIKE '%" + search + "%'";
      condition += "lastName LIKE '%" + search + "%'";
  }

  if(fromDate != null && toDate != null && fromDate != '' && toDate != '')
  {
      condition += "date BETWEEN" + fromDate + "AND" + toDate;
  }

  const query = "";
  query = " SELECT * from TABLE where" + condition;
  sequelize.query(query, type: sequelize.QueryTypes.SELECT)
  .then( ...)

 }
}

我想做一个场景，即使条件为空，查询也应该返回数据库。最初我想在条件变量中使用 if 来检查条件变量是否有任何内容。

if(condition != '' && condition != null) {
    query = " SELECT * from TABLE where" + condition;
} else {
    query = " SELECT * from TABLE " ;
}

但它没有按预期工作(由于 JavaScript 的异步特性)。我想自己创建一个 SQL 查询来检查 where 子句中是否存在任何条件，如果没有，则它应该返回整个表。任何帮助，将不胜感激

Answer 1

标准技巧是将 1=1 添加到条件列表中。并且(也许)稍后使用 AND condition 附加额外的条件:

const condition = " 1=1";

  if(search != null && search != '')
  {
      condition += " AND id LIKE '%" + search + "%'";
      condition += " AND fromDate LIKE '%" + search + "%'";
      condition += " AND toDate LIKE '%" + search + "%'";
      condition += " AND firstName LIKE '%" + search + "%'";
      condition += " AND lastName LIKE '%" + search + "%'";
  }

  if(fromDate != null && toDate != null && fromDate != '' && toDate != '')
  {
      condition += " AND date BETWEEN " + fromDate + " AND " + toDate;
  }

  const query = "";
  query = " SELECT * from TABLE where" + condition;
  sequelize.query(query, type: sequelize.QueryTypes.SELECT) ...