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

Question

I have this native SQL written in doctrine

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 (:taskType)

I have to write it in native SQL because int_task_type_id is the discriminator column in a hierarchical model class.

The problem is that i cannot do the following:

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

or this:

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

How can I solve this?

Answer 1

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