在 Express JS 中,我们有时需要根据用户提供的可选过滤条件构建 MySQL 查询。例如,用户可以选择根据日期范围、文本字段等过滤结果。
以下是构建 MySQL 查询的步骤:
const { startDate, endDate, searchText } = req.query;
let whereClause = '';
const filterValues = [];
if (startDate) {
whereClause += 'start_date >= ? AND ';
filterValues.push(startDate);
}
if (endDate) {
whereClause += 'end_date <= ? AND ';
filterValues.push(endDate);
}
if (searchText) {
whereClause += 'text LIKE ? AND ';
filterValues.push('%' + searchText + '%');
}
// Remove the trailing AND if there are filters
if (whereClause.endsWith('AND ')) {
whereClause = whereClause.slice(0, -4);
}
const query = `SELECT * FROM my_table
${whereClause ? `WHERE ${whereClause}` : ''}`;
此处的 ${whereClause ?
WHERE ${whereClause} : ''}
语法是一个 JavaScript 模板字面量,如果 whereClause 存在,则查询语句会包含 WHERE 子句,否则查询语句不包含 WHERE 子句。
const [rows, fields] = await connection.execute(query, filterValues);
完整的代码示例:
app.get('/my-route', async (req, res) => {
const { startDate, endDate, searchText } = req.query;
let whereClause = '';
const filterValues = [];
if (startDate) {
whereClause += 'start_date >= ? AND ';
filterValues.push(startDate);
}
if (endDate) {
whereClause += 'end_date <= ? AND ';
filterValues.push(endDate);
}
if (searchText) {
whereClause += 'text LIKE ? AND ';
filterValues.push('%' + searchText + '%');
}
if (whereClause.endsWith('AND ')) {
whereClause = whereClause.slice(0, -4);
}
const query = `SELECT * FROM my_table
${whereClause ? `WHERE ${whereClause}`
上一篇:BuildingaMultiDiGraphinNetworkX
下一篇:BuildinganaggregatorusingRShiny.Alternatives? RShiny构建聚合器,有什么替代方案?