PDO's WHERE IN (?) issue due to unsupported array parameters
franzose opened this issue · 3 comments
franzose commented
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 commented
Latitude already supports in()
with arrays:
$criteria = field('id')->in(...$array);
shadowhand commented
Also refer to the php documentation for argument unpacking.
franzose commented
@shadowhand wow, sorry, my fault...