How can I use a native query with an array IN parameter in Doctrine2

In case this helps others:

I have just stumbled into this problem with an old Zend 1.11 build using Doctrine 2.0.5 (ish). I found the above did not work with my PDO connection. I believe PDO is adding quotes round the parameter and so you can not use the above.

The only approach I have found that works is:

$types = []; $binds = []; $values = []; foreach ($taskTypes as $taskType) { $types[] = \PDO::INT; $binds[] = '?'; $values[] = $taskType; } // Get Entity Manager from wherever $conn = $entityManager->getConnection(); $stmt = $conn->executeQuery("SELECT COUNT(DISTINCT t.int_task_type_id) as a_count FROM tbl_xref_people_task t WHERE t.bit_completed = true AND t.int_people_id = :peopleId AND t.int_task_type_id IN (" . implode(',', $binds) . ")", $values, $types ); $stmt->execute(); $stmt->fetchAll(); // Fetch 

Try this :

$query = 'SELECT COUNT(DISTINCT t.int_task_type_id) as a_count FROM tbl_xref_people_task t WHERE t.bit_completed = :bitCompleted AND t.int_people_id = :peopleId AND t.int_task_type_id IN (:taskTypes)'; $params = [ 'bitCompleted' => true, 'peopleId' => $peopleId, 'taskTypes' => $taskType ]; $types = [ 'bitCompleted' => Doctrine\DBAL\ParameterType::BOOLEAN, 'peopleId' => Doctrine\DBAL\ParameterType::INTEGER, 'taskTypes' => Doctrine\DBAL\Connection::PARAM_STR_ARRAY ]; $conn = $entityManager->getConnection(); $stmt = $conn->executeQuery($query, $params, $types); $resultset = $stmt->fetchAllAssociative(); 

I think you can do that easily by using:

$query->setParameter('taskType', $taskType);

Doctrine will automatically convert $taskType into proper format.