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)

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

To copy to clipboard, switch view to plain text mode 

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

SELECT * FROM stuff WHERE id IN (:ids)

SELECT * FROM stuff WHERE id IN (:ids)

To copy to clipboard, switch view to plain text mode 

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;

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

To copy to clipboard, switch view to plain text mode 

which uses following piece of code:

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

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

    return *this;
}

To copy to clipboard, switch view to plain text mode 

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)

SELECT * FROM stuff WHERE id IN (SELECT stuff_id FROM foreigntable WHERE value < :value)

To copy to clipboard, switch view to plain text mode