sqlite 带条件AND条件的查询

n3h0vuf2 于 2023-08-06 发布在 SQLite

关注(0)|答案(1)|浏览(136)

字段status、trade、department、urgency_level和room我想从中筛选（都是可选的）。我的查询：

let queryParams = '';
let filterOptions : IFilterProps = params.filterOptions;
if (filterOptions.status && filterOptions.status != '-1') {
  queryParams = `status = '${filterOptions.status}'`
}
if (filterOptions.priority && filterOptions.priority != '0') {
  queryParams = queryParams + `AND urgency_level = '${filterOptions.priority}'`
}
if (filterOptions.department) {
  queryParams = queryParams + `AND work_type = '${filterOptions.department}'`
}
if (filterOptions.trade) {
  queryParams = queryParams + `AND location = '${filterOptions.trade}'`
}
if (filterOptions.room) {
  queryParams = queryParams + `AND room_id = '${filterOptions.room}'`
}

selectQuery = await GetDataFromLocalDataBase('SELECT * FROM MaintenancesHistory WHERE '+queryParams, []);

export const GetDataFromLocalDataBase = (sql, params = []) => new Promise((resolve, reject) => {
 db.transaction((tx) => {
  tx.executeSql(sql, params, (tx, results) => {
    resolve(results);
 },
   (error) => {
    reject(error);
    });
   });
 });

字符串
如果只选择department，查询将是AND work_type = 'Housekeeping'。我如何连接所有可能的组合？

sqlite

来源：https://stackoverflow.com/questions/76777982/query-with-conditional-and-condition

1条答案

按热度按时间

91zkwejq1#

要在SQL查询中连接所有可能的过滤器选项组合，可以修改代码，根据每个过滤器选项的存在动态构建queryParams字符串。这样，您就可以处理用户提供的任何过滤器组合。

let queryParams = '';
let filterOptions: IFilterProps = params.filterOptions;
const filters = [];

if (filterOptions.status && filterOptions.status !== '-1') {
  filters.push(`status = '${filterOptions.status}'`);
}
if (filterOptions.priority && filterOptions.priority !== '0') {
  filters.push(`urgency_level = '${filterOptions.priority}'`);
}
if (filterOptions.department) {
  filters.push(`work_type = '${filterOptions.department}'`);
}
if (filterOptions.trade) {
  filters.push(`location = '${filterOptions.trade}'`);
}
if (filterOptions.room) {
  filters.push(`room_id = '${filterOptions.room}'`);
}

if (filters.length > 0) {
  queryParams = filters.join(' AND ');
}

const selectQuery = await GetDataFromLocalDataBase(
  `SELECT * FROM MaintenancesHistory` + (queryParams ? ` WHERE ${queryParams}` : ''),
  []
);

export const GetDataFromLocalDataBase = (sql, params = []) =>
  new Promise((resolve, reject) => {
    db.transaction(
      (tx) => {
        tx.executeSql(
          sql,
          params,
          (tx, results) => {
            resolve(results);
          },
          (error) => {
            reject(error);
          }
        );
      },
      (error) => {
        reject(error);
      }
    );
  });

字符串

赞(0）回复(0）举报 2023-08-06

我来回答

sqlite 带条件AND条件的查询

1条答案

相关问题

热门标签

最新问答