Since I stumbled across this the other day, here’s how you build an SQL query with a WHERE IN condition for an array of integers.
This is surprisingly not intuitive, as you cannot simply pass an array of integers to a prepared statement. Instead, you need to add special binding types to inform Doctrine that this is indeed an array of integers.
It’s very well explained in the documentation under the Parameters Conversion section:
https://www.doctrine-project.org/projects/doctrine-dbal/en/current/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion
But it is rather hard to find and it took me a while.
The trick is to add the array binding types to the the types parameters to the query method. In the case of integers, it is \Doctrine\DBAL\ArrayParameterType::INTEGER.
Now Doctrine knows how to handle the types in the array while binding the values.
Example:
$sql = 'SELECT * FROM items WHERE id IN (?)';
$numbersAsStrings = "1,2,3";
$numbers = array_map('intval', explode(',', $numbersAsStrings));
$users = $con->fetchAllAssociative(
$sql,
[$numbers],
[ArrayParameterType::INTEGER]
);Code language: PHP (php)If you omit the types, Doctrine will simply try to bind the array as string, which will fail and you get this error:
Array to string conversion
If you try to pass it as a comma-separated string yourself and omit the types like $numbers = implode(',',array_map('intval', explode(',', $numbersAsStrings))); You will get an error because Doctrine expects an array of values for the IN clause and not a string.
Error:
count(): Argument #1 ($value) must be of type Countable|array, string given
I hope this helps someone finding out how to make WHERE IN SQL queries with Doctrine more quickly than me next time.