Qt - How to bind a QList to a QSqlQuery with a "WHERE ... IN" clause?

Question

Note: this is with SQLite, although I expect the problem is on the Qt side.

First, I set up a database table from the SQLite command line tool:

sqlite> create table testtable ( id INTEGER PRIMARY KEY NOT NULL, state INTEGER );
sqlite> insert into testtable (state) values (0);
sqlite> insert into testtable (state) values (1);
sqlite> insert into testtable (state) values (9);
sqlite> insert into testtable (state) values (20);

Then I test my query:

sqlite> SELECT id,state FROM testtable WHERE state IN (0,1,2);
1|0
3|1

(Those are expected results.)

Then I run this C++ code:

void runQuery() {
        QSqlQuery qq;
        qq.prepare( "SELECT id,state FROM testtable WHERE state IN (:states)");
        QList<QVariant> statesList = QList<QVariant>();
        statesList.append(0);
        statesList.append(1);
        statesList.append(2);
        qq.bindValue(":states", statesList);
        qq.exec();
        qDebug() << "before";
        while( qq.next() ) {
            qDebug() << qq.value(0).toInt() << qq.value(1).toInt();
        }
        qDebug() << "after";
}

which prints this:

before
after

No rows were printed. I assume this is because I can't bind a list directly to a placeholder in an "in" clause. But is there a way to do it? I haven't been able to find anything about this.

Answer 1

Never mind my question. I think what I am trying to do is not possible with prepared statements, regardless of the framework or RDBMS. You can do "WHERE x IN (?)", but then the '?' refers to an single value -- it cannot be a list of values; or you can do "WHERE x IN (?,?,?), and each '?' needs to be bound separately.

Example:

QString const queryText = "SELECT id, firstname FROM users WHERE id IN (%1)";

QVector<int>     const ids { /* ... */ };
QVector<QString> const placeholders(ids.size(), "?");

QSqlQuery query(db);
query.prepare(queryText.arg(QStringList::fromVector(placeholders).join(", ")));

for (auto const & i : ids)
    query.addBindValue(i);

query.exec();

Answer 2

I have been looking for a way to do this too for a while now, and Google wasn't very helpful. I started playing around with it and it turns out it is indeed possible, to a limited degree at least. It is tested only with PostgreSQL and SQLite, so I do not know about other RDBMS. My case concerns only integer keys, but should theoretically work for other types as well.
The way to do this is build an array manually and bind it to a variable. Say I want to select multiple users by their ids from a table, such as SELECT id, firstname, lastname FROM users WHERE id = ANY(:id). Here's how it can be done.

QList<int> ids; // A list of IDs to select
ids << 1 << 5 << 7;

// Create strings from list
QStringList idstrings;
foreach(int id, ids) {
    idstrings << QString::number(id);
}
QString numberlist = idstrings.join(",");

// Create, prepare and execute the query
QSqlQuery sql;
sql.prepare("SELECT id, firstname, lastname FROM users WHERE id = ANY(:id)");
sql.bindValue(":id", numberlist);
sql.exec();

// Now this is possible
while( sql.next() ) {
    qDebug() << sql.value(0).toInt() << sql.value(1).toString() << sql.value(2).toString();
}

Typed from memory, but should be fine. I know this reply is extremely late, but hopefully this post helps someone else out there. The snippet above only works with PostgreSQL. However it is be possible to adapt this to other databases as well, depending on their array support.

For SQLite replace the SQL query with:

sql.prepare("SELECT id, firstname, lastname FROM users WHERE id IN (:id)");