英文:
Query with conditional AND condition
问题
我可以帮你翻译代码部分，以下是翻译好的内容：
// 从以下字段中过滤：`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'。如何连接所有可能的组合？
英文:
Fields status, trade, department, urgency_level and room I want to filter from (all are optional). My query:
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);
});
});
});
When I choose department only, query will be AND work_type = 'Housekeeping'. How can I concatenate all possible combinations?
答案1
得分: 1
为了在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);
}
);
});
以上是修改后的代码，它会根据过滤选项的存在动态构建查询语句中的queryParams字符串。这样，您可以处理用户提供的任何过滤组合。
英文:
To concatenate all possible combinations of filter options in your SQL query, you can modify your code to dynamically build the queryParams string based on the presence of each filter option. This way, you can handle any combination of filters provided by the user.
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);
}
);
});
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论