CODEWITHSUNDEEP
phpdatabasedoctrine-orm
Hernan Rajchert
Hernan Rajchert

Reputation: 926

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

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?

Upvotes: 9

Views: 6910

Answers (3)

Arnaud TLJ
Arnaud TLJ

Reputation: 11

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

Upvotes: 1

PanPipes
PanPipes

Reputation: 4594

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

Upvotes: 2

subosito
subosito

Reputation: 3470

I think you can do that easily by using:

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

Doctrine will automatically convert $taskType into proper format.

Upvotes: 0

Related Questions