Array parameters for IN clause

[enumag]

In some cases I want to use IN (...) with a dynamic number of values. For instance:

// $ids is int[]
$result = $link->execute('SELECT * FROM articles WHERE id IN (:ids)', ['ids' => $ids]);

At the moment this returns nothing - no error but no result either even if there is one.

I know that this might be non-trivial to implement, however Doctrine does have this feature. See PARAM_INT_ARRAY / PARAM_STR_ARRAY in Doctrine documentation. It is done through kind of a hack though - the database receives WHERE id IN (?, ?, ?, ?, ?, ?) with several parameters instead.

I'm not quite sure how this library works internally. Do you think this would be possible to implement somehow? I kinda dislike using a dynamic number of question marks in the SQL query...

[trowski]

Supporting this directly in existing methods is not possible. Postgres fields can be arrays of values (which is probably why no error is produced from your example, though you'd think you'd get a field-type mismatch error). This makes it impossible to know if an array in the field values should be expanded or used as the column value. Prepared statements also present a problem, since differing number of values for the IN clause would require a different prepared statement.

It is done through kind of a hack though - the database receives WHERE id IN (?, ?, ?, ?, ?, ?) with several parameters instead.

I think that's the only way to support this. A separate class or function could wrap a Link object and provide this functionality.

[enumag]

I see. Better to implement it in userland code then. Thanks!

[trowski]

This file contains the regex and functions used to parse placeholders from an SQL string.

If you do write something to do this, please submit a PR! I'm sure others would find it useful!

[PNixx]

Tell me how can I implement this in code?