How to use WHERE IN with Doctrine 2
When building queries in Doctrine 2 using QueryBuilder, developers frequently encounter issues when trying to pass arrays directly into WHERE clauses involving IN. A common symptom is the runtime error: Invalid parameter number: number of bound variables does not match number of tokens. This occurs because Doctrine's parameter binding mechanism expects a specific contract between placeholders in the generated SQL and the values you provide via setParameter().
In your example, the logic attempts to count ratings where the winner or loser matches a list of IDs. The code wraps the $ids array into a single placeholder ?1 and then binds the entire array to that single token:
$qb->add('where', $qb->expr()->in('r.winner', array('?1')));
// ...
$qb->setParameter(1, $ids);
Doctrine generates the SQL string with only one placeholder: WHERE r.winner IN (?1). However, when you pass an array like [566, 569, 571] to setParameter, Doctrine tries to bind that single parameter as a list. Depending on the underlying database driver and how the driver handles prepared statements, it often interprets this mismatch as an invalid number of tokens for the specific parameter definition. The SQL engine expects three distinct placeholders (?, ?, ?) for three values, but you provided only one slot with a complex value type that the driver doesn't automatically expand in this context.
The robust solution is to let Doctrine handle the placeholder generation automatically. Instead of manually constructing the string array('?1'), you should pass the raw array directly to the in() expression method. Doctrine's QueryBuilder is designed to iterate over the provided collection and generate the correct number of placeholders (?, ?, ?) for every element in your input array.
Here is the corrected implementation:
public function getCount($ids, $outcome)
{
if (!is_array($ids)) {
$ids = array($ids);
}
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('COUNT(r.id)')
->from('\My\Entity\Rating', 'r');
// Pass the raw array directly to in()
if ($outcome === 'wins') {
$qb->where('r.winner IN (:ids)');
$qb->setParameter('ids', $ids);
} elseif ($outcome === 'fails') {
$qb->where('r.loser IN (:ids)');
$qb->setParameter('ids', $ids);
}
return $qb->getQuery()->getSingleScalarResult();
}
Notice the shift from array('?1') to a named parameter :ids. When you use named parameters (prefixed with a colon), Doctrine automatically replaces the placeholder count based on the size of the $ids array. If you pass three IDs, it generates IN (?, ?, ?). If you pass one ID, it generates IN (?). This approach decouples your PHP logic from the specific SQL syntax required by the database driver.
Using named parameters also offers better readability and maintainability compared to positional parameters (?1, ?2). It makes the code self-documenting; anyone reading the query can see that $ids is meant for the IN clause without guessing which number corresponds to which value.
While Doctrine supports positional parameters, mixing them with complex array bindings often leads to the "number of bound variables" error seen in your logs. By sticking to named parameters and passing raw arrays to in(), you ensure that the ORM handles the SQL construction correctly across different database engines like MySQL, PostgreSQL, or SQLite. This pattern is a standard best practice for avoiding parameter binding errors in Doctrine 2 applications.
