QSqlQuery bindValue and SELECT WHERE IN

[helloworld]

Hi!

Is it possible to use bindValue with a SELECT statement like:



SELECT * FROM stuff WHERE id IN (1, 2, 3, 4)


I have tried several options, including using a QList<QVariant> with:



SELECT * FROM stuff WHERE id IN (:ids)


but no luck :(

Thanks!

[wysota]

No, I don't think so.

[nephre]

Perhaps You could try using unnamed parameters. In my code I use something like this:



Query q("SELECT * FROM tag WHERE tag = ?", connection);
q << name;


which uses following piece of code:



Query &Query::operator <<(QVariant &param)
{
queryObject.bindValue(_paramBound++, param);

return *this;
}


I know this solution is not perfect and has some flaws, but for sure it can be used this way to pass QLists as list of values to bind query parameters.

[wysota]

That's not the point here. The point is that you want to be able to bind a list of ids separated by commas to a single placeholder. With your solution you'd still need to know how many items the list has to count placeholders that need to be put in the query.

[nephre]

Of course, I'm aware of that, it's not a clean solution. Someone could play with parsing SQL query and replacing single unnamed parameter with QList::count() parameters, then binding them all. Question is: is it worth? Because for me, it's dirty for sure.

Since I don't have much experience with Qt/C++ and SQL programming, so far I never needed to use queries with variable abount of parameters. Maybe some ORM framework would fit here, but I don't know how ORM offer looks in C++ world.

[wysota]

A solution to this concrete problem would probably be to use a subquery returning the list. Something like:
SELECT * FROM stuff WHERE id IN (SELECT stuff_id FROM foreigntable WHERE value < :value)