在处理潜在的未定义值时创建 SQL SELECT 查询的最佳做法

Question

我目前正在通过pg-promise使用PostgreSQL创建一个NodeJS网站。

我有一个带有 HTML 表单的页面，其中包含用于选择变量以使用各种字段搜索数据库的复选框。然后，这些结果与pg-promise一起输入到 SQL 查询中，预期的行为是将结果以 JSON 格式传递回用户。

一个非常小的工作示例如下。

网页表单：

<form action="/search" method="get">
<fieldset>
<legend>Variable A</legend>
<div>
<input type="checkbox" name="variable_a" value="apple">
<label for="variable_a">
Apple
</label>
</div>
<div>
<input type="checkbox" name="variable_a" value="orange">
<label for="variable_a">
Orange
</label>
</div>
</fieldset>
<fieldset>
<legend>Variable B</legend>
<div>
<input type="checkbox" name="variable_b" value="pear">
<label for="variable_b">
Pear
</label>
</div>
<div>
<input type="checkbox" name="variable_b" value="banana">
<label for="variable_b">
Banana
</label>
</div>
</fieldset>
<button type="submit">Search</button>
</form>

由此创建了一个如下所示的 URL/search?variable_b=pear&variable_b=banana

我遇到的问题是在尝试创建一个"全部捕获"SQL SELECT 查询来处理此搜索时。

这是我在pg-promise中创建的SQL查询：

router.get('/search', function(req, res, next) {
db.any(`SELECT * FROM food 
WHERE variable_a IN ($1:csv)
AND variable_b IN ($2:csv)`, [req.query.variable_a, req.query.variable_b])
.then(result=>res.send(result))
.catch();
});

给定/search?variable_b=pear&variable_b=bananaURL，此操作失败，但适用于以下 URL/search?variable_a=apple&variable_b=banana。

这无疑是因为在上面的示例中，req.query.variable_a未定义，因为未选中任何复选框，并且 SQL 查询会随着IN ()而失败。我也许应该补充一点，如果复选框未定义variable_a或variable_b，在这种情况下，预期的行为是所述列上没有过滤器。

我的问题是处理这个问题的最佳方法是什么？

我觉得我可能会创建很多 if/else 逻辑来处理潜在的未定义req.query变量和生成的 SQL 查询，但这似乎很混乱且不优雅。

Answer 1

此问题与此处记录的问题相同：https://github.com/vitaly-t/pg-promise/issues/442

基本上，pg-promise 查询格式化引擎会根据您的格式参数生成 SQL。它不会对生成的 SQL 进行任何语法验证。

您正在生成IN ()，这是无效的 SQL，因此您会收到错误。

您应该检查变量是否存在，甚至不要在缺少变量时尝试生成此类查询，因为您的查询将无法产生任何好处。

例：

router.get('/search', (req, res, next) => {
const variables = ['variable_a', 'variable_b', 'variable_c'];
const conditions = variables.filter(v => v in req.query)
.map(v => pgp.as.format('$1:name IN ($2:csv)', [v, req.query[v]]))
.join(' AND ');
conditions = conditions && 'WHERE ' + conditions;
db.any('SELECT * FROM food $1:raw', conditions)
.then(result => res.send(result))
.catch(error => {/* handle the error */});
});

可以有其他解决方案，因为 pg-promise 是非常通用的，它不会限制您处理此问题的方式。

例如，代替这个：

v => pgp.as.format('$1:name IN ($2:csv)', [v, req.query[v]])

您可以这样做：

v => pgp.as.name(v) + ' IN (' + pgp.as.csv(req.query[v]) + ')';

这将产生相同的结果。随便你！;)

Answer 2

第一个 - 您的输入将仅保留最后一个选定的值

<input type="checkbox" name="variable_a" value="apple">

或者您应该使用带有 [] 的名称来通知它是一个数组

第二 - 你可以在参数或变量中使用？语句

req.query.variable_a ? req.query.variable_a : null

在你的SQL中 - 如果你没有发送任何变量 - 你想要得到结果 导致其严格的 AND 语句 - 变量未定义 - 查询返回 false