PDO's WHERE IN (?) issue due to unsupported array parameters

[franzose]

Hi! Many thanks for the library.

Example query:

$factory->select()->from('foo')->where(field('bar')->in([1,2,3,4]));

Generated SQL is:

SELECT * FROM foo WHERE bar IN (?)

Parameters: [1,2,3,4]. The issue is that PDO does not support array parameters like this one, so I suggest to enhance the Latitude\QueryBuilder\Partial\Parameter class to handle field('bar')->in([1,2,3,4]) properly. It could be done like so:

public function __construct($value)
{
    if (\is_bool($value) || $value === null) {
        $this->sql = var_export($value, true);
    } elseif (\is_array($value)) {
        $this->sql = str_repeat('?,', \count($value) - 1) . '?';
        array_push($this->params, ...$value);
    } else {
        $this->params[] = $value;
    }
}

As of now I had to write some functions and classes to override the ‘invalid’ behaviour:

function field($name): CriteriaBuilder
{
    return new CriteriaBuilder(identify($name));
}

function array_param($value): StatementInterface
{
    // custom class with the logic mentioned above
    return isStatement($value) ? $value : new ArrayParameter($value);
}

function array_param_all(array $values): array
{
    return array_map('array_param', $values);
}

function array_listing(array $values, string $separator = ', '): Listing
{
    return new Listing($separator, ...array_param_all($values));
}

I can't say if the change could break backward compatibility but it would be nice to have parameters binding fixed.

I can submit PR if you like.

[shadowhand]

Latitude already supports in() with arrays:

$criteria = field('id')->in(...$array);

[shadowhand]

Also refer to the php documentation for argument unpacking.

[franzose]

@shadowhand wow, sorry, my fault...