Лучшие практики для создания запросов SQL SELECT при обработке потенциальных неопределенных значений

Question

В настоящее время я создаю сайт NodeJS с использованием PostgreSQL через pg-обещание.

У меня есть страница с HTML-формой с флажками для выбора переменных для поиска в базе данных с использованием различных полей. Затем они подаются в SQL-запрос с pg-promise и предполагаемое поведение - результаты передаются обратно пользователю в формате JSON.

Очень минимальный рабочий пример будет следующим.

HTML-форма:

<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 "перехватить все" для обработки этого поиска.

Это SQL-запрос, который я создал в pg-promise:

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=banana URL, но работает, скажем, следующий 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-обещание генерирует 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-обещание очень универсально, оно не ограничивает ваш подход к этому.

Например, вместо этого:

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">

или вы должны использовать имя с [], чтобы сообщить, что это массив

второе - можно использовать ? заявление только внутри params или var

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

А внутри вашего SQL - если вы не отправили ни одного из vars - вы хотите получить результат, потому что он имеет строгий оператор AND - var undefined - запрос возвращает false