How can I use my own query with an array parameter IN in Doctrine2

Question

I have this native doctrine SQL

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 need to write it in native SQL because int_task_type_id is the discriminator column in the model hierarchical class.

The problem is that I cannot do the following:

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

or that:

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

How can i solve this?

Answer 1

If this helps others:

I just stumbled upon this issue with the old Zend 1.11 build using Doctrine 2.0.5 (ish). I found that the above does not work with my PDO connection. I believe that PDO adds quotes around the parameter and therefore you cannot use the above.

, , :

$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

Answer 2

, , :

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

Doctrine $taskType .