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?

PanPipes · Accepted Answer · 2015-06-26 16:18:57Z

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

Arnaud TLJ · Accepted Answer · 2023-11-24 18:11:36Z

1

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();

answered Nov 24, 2023 at 18:11

Arnaud TLJ

111 bronze badge

Comments

subosito · Accepted Answer · 2011-10-13 14:37:06Z

0

I think you can do that easily by using:

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

Doctrine will automatically convert $taskType into proper format.

answered Oct 13, 2011 at 14:37

subosito

3,4802 gold badges26 silver badges12 bronze badges

1 Comment

Hernan Rajchert Over a year ago

Not in this case, it complains about changing array to string.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related