如何在Doctrine2中使用带有数组IN参数的本机查询

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

我必须在本机SQL中编写它，因为int_task_type_id是分层模型类中的鉴别器列。

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?

我怎么解决这个问题？

2 个解决方案

#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.

我刚刚使用Doctrine 2.0.5（ish）使用旧的Zend 1.11版本偶然发现了这个问题。我发现上面的内容不能与我的PDO连接一起使用。我相信PDO会在参数周围添加引号，因此您无法使用上述内容。

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

#2

I think you can do that easily by using:

我认为您可以通过以下方式轻松完成：

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

$ query-> setParameter（'taskType'，$ taskType）;

Doctrine will automatically convert $taskType into proper format.

Doctrine会自动将$ taskType转换为正确的格式。

#1