Postgres параметры SELECT запроса списком.

[RedDog]

Господа, как правильно в SELECT запрос передать список значений для фильтрации?

Вот такой код пишет, что ему мало параметров

    

Код

C++ (Qt)
QVariantList keys
    {
    QUuid("08a34f7d-7c20-4c58-a034-fba625ee11eb"),
    QUuid("96f86679-9b77-456d-8204-644e6f500d4c"),
    QUuid("a98c85b0-80b5-4a32-bb1f-7c3a7196cae8")
    };
 
    const QString sqlText = R"(SELECT * FROM "TestTable" WHERE "Id" IN(?);)";
 
    QSqlQuery select(m_database);
 
    select.prepare(sqlText);
    select.addBindValue(keys);
 
    if (!select.exec())
        qDebug() << select.lastError().text();

"ОШИБКА:  неверное число параметров для подготовленного оператора \"qpsqlpstmt_1\"\nПОДРОБНОСТИ:  Ожидалось параметров: 1, получено: 0.

попробовал вот через преобразования в текст делать типа такого:

Код:

SELECT * FROM "TestTable" WHERE "Id"::text IN(?);

тоже не работает.

В чем моя ошибка?

PS: запросы на вставку/удаление/обновление с переданными списками execBatch() работают.


Решение:

Код

SQL
SELECT * FROM "TestTable" WHERE "Id" IN(SELECT unnest(string_to_array(?, ','))::uuid);

Ключи преобразовать в строку с разделителем запятой, и эту строку передать как параметр в запрос.

[schmidt]

Ну, разумеется

С каждым параметром запроса можно связать только одно значение.

https://doc.qt.io/qt-5/sql-sqlstatements.html
If you want to insert many records at the same time, it is often more efficient to separate the query from the actual values being inserted. This can be done using placeholders. Qt supports two placeholder syntaxes: named binding and positional binding. Here's an example of named binding:

Код:

    QSqlQuery query;
    query.prepare("INSERT INTO employee (id, name, salary) "
                  "VALUES (:id, :name, :salary)");
    query.bindValue(":id", 1001);
    query.bindValue(":name", "Thad Beaumont");
    query.bindValue(":salary", 65000);
    query.exec();

SELECT * FROM "TestTable" WHERE "Id" IN(SELECT unnest(string_to_array(?, ','))::uuid);

Если вам заранее известны ваши UUID, то запрос делает лишнюю работу, вызывая unnest() и string_to_array(). Почему бы не хранить нужные вам UUID в другой таблице?

SELECT * FROM "TestTable" WHERE "Id" IN( SELECT "uuid" from needed_uuids )

Если список UUID хранится в исходном тексте, придется перекомпилировать программу, чтобы изменить условие запроса. Если нет возможности добавить в БД нужную таблицу, вынесите идентификаторы в конфиг приложения (есть QSettings). Тогда условие IN в запросе можно строить, используя QStringList::join().

Конечно, придется самому позаботиться и о проверке того, что включается в SQL-запрос: а содержит ли список корректные UUID или кто-то зловредный переписал их и включил опасный SQL-код? Например, пройтись по QStringList и попробовать создать из каждого QUuid. Делать такую проверку достаточно один раз - при запуске приложения во время чтения конфига.

Тема: можно ли записать QStringList в QSettings одним махом?
http://www.prog.org.ru/topic_14123_0.html

[RedDog]

Клиент запрашивает с сервера Id-шники, затем он их фильтрует, и посылает на сервак запрос на получение объектов по списку Id-шников, сервак, соответственно должен выдать только те объекты, которые есть в списке от клиента.
Так же клиент может независимо, свой список сгенерить по связям между объектами, или из своего кеша.
В общем случае, на сервере я не знаю, что запросит клиент.

[sergek]

Просто сделайте текст sql-запроса без параметров. Например:

Код

C++ (Qt)
        QStringList keys;
        QString list = keys.join("','");
        QString sql = "SELECT * FROM TestTable WHERE Id IN('"+list+"')";
 
        QSqlQuery query(m_database);
        query.exec(sql);
 
        QSqlRecord rec = query.record();
        while(query.next()){
            QString id = query.value(rec.indexOf("id")).toString();
            ...
        }
 

Здесь для удобства я QVariantList заменил на QStringList, чтобы использовать join. Обратите внимание, в условии IN список строк, поэтому каждый uuid заключен в одинарные кавычки.
Писал в блокноте, могут быть синтаксические ошибки.

[RedDog]

Так и было изначально. Но мое имхо, что это не совсем красиво и постгря будет кажлый раз компилять новый текст запроса. Когда как параметризированный, она компиляет единожды, и потом в него просто подставляет параметры.

[sergek]

Но мое имхо, что это не совсем красиво и постгря будет кажлый раз компилять новый текст запроса.

Поясните.

[RedDog]

Если по простому prepare() на текст запроса не надо каждый раз вызывать

[schmidt]

1. Если данные приходят от клиента, обязательна проверка (validation) перед включением в запрос.

Код

C++ (Qt)
QStringList keys;
foreach (QString param, http_post.uuids) {
    keys.append(sqlEscapeString(param));
}
// И только после экранирования можно так:
QString list = keys.join("','");
QString sql = "SELECT * FROM TestTable WHERE Id IN('"+list+"')"
 

2. Мне все же кажется, что prepare() хорош для запроса, число параметров которого заранее известно:

Код

SQL
SELECT * FROM TABLE WHERE c1 = ? AND c2 = ? AND c3 = ?
 

В вашем случае IN - это не параметр, а условие с подзапросом. Число uuid заранее не известно - придут от клиента. Если использовать

Код

C++ (Qt)
QSqlQuery select("SELECT * FROM "TestTable" WHERE "Id" IN(SELECT unnest(string_to_array(?, ','))::uuid);");
select.addBindValue(comma_separated_string);
 

то вы, скорее всего, выиграете от prepare() - подготовленный запрос с одним параметром типа "строка" скомпилируется однажды, но за это заплатите временем на unnest и string_to_array. Если очень надо, поиграйтесь с замерами по времени на разных вариантах.

[RedDog]

1. От клиента приходит список QUuid-ов. Зачем еще валидация?
2. Запросы в параметризированном виде выполняются менее 20мс. Для моих целей этого более чем достаточно.
3. Подобная запись в моей текущей архитектуре позволяет унифицировать много моментов.