javascript - 如何在 WHERE 子句中使用动态过滤器创建 sql 查询

Question

我目前在 aws lambda/node js 上，我的数据库配置了 rds/auroradb，它使用一般的 mysql 语法。我有一个前端，它有一个带有多个过滤器的表，其中包含下拉/复选框/排序等，我正在尝试创建一个动态 sql 查询，该查询在我的查询的 WHERE 子句中实现这些过滤器/条件。我正在寻找的是一个可以轻松为我处理这个问题的库。到目前为止，我尝试的是一个自定义函数来为我处理这个问题。但这会导致很多问题，并且似乎是动态构建查询的不可靠方式。

const buildPatientQuery = (data) => {
  const queryBase = 'SELECT * FROM (patient as p JOIN patient_score as s ON p.patient_id=s.patient_id)';
  const { filters, sortBy } = data;
  let filterQuery = "WHERE";

  //Build query for dropdown and checkbox filters
  if (filters.length) {
    filters.map(item => {
      const { checkboxSelected, dropdownSelected, qf, fromToMode, individual, from, to, key } = item;
      if (qf) {
        console.log('Quick filter');
      }
      else {
        if (checkboxSelected) {
          if (queried) {
            console.log('Checkbox filter - queried');
          }
          else {
            console.log('Checkbox filter - not queried');
          }
        }
        else if (dropdownSelected) {
          //Check if range
          if (fromToMode) {
            filterQuery = `${filterQuery} AND ${key} BETWEEN ${from} AND ${to}`;         
          }
          //Check if individual item
          else if (individual) {
            filterQuery = `${filterQuery} AND ${key}='${direction}'`;
          }
        }
      }
    });
  }
  else {
    filterQuery = ""
  }

  //Build query for sorting
  const { name, direction } = sortBy;
  const sortDirection = direction === "ascending" ? "ASC" : "DESC";
  filterQuery = `${filterQuery} ORDER BY ${name} ${sortDirection}`;

  //Return final query build
  return queryBase + " " + filterQuery;
}

module.exports = buildPatientQuery;

标签： javascriptmysqlsqlaws-lambdaamazon-rds