我试图用条令entitymanager for IN子句运行原始sql查询，如下所示。
$idSArray = Array ( [0] => 1 [1] => 2 )
$stmt = $this->getDoctrine()->getEntityManager()
->getConnection()
->prepare('SELECT t1.id , t1.name , t2.start_date , t2.end_date
FROM table1 t1 , table2 t2
WHERE t1.id = t2.matchId AND t1.id IN (:ids)');
$params = array(
'ids' => implode( ",", $idSArray )
);
$stmt->execute($params);
$results = $stmt->fetchAll();
但我只得到Id为1的结果。如果我将WHERE IN条件硬编码为
WHERE t1.id = t2.matchId AND t1.id IN (1,2)');
然后得到两个ID的结果。有人能告诉我我在传递$params数组时做错了什么吗。我还打印了内爆结果，输出1,2。所以我找不到错误，也找不到用IN子句执行原始sql查询的方法。
答案：
- 参数转换列表
- DoctrineDBALTypes转换
所以你至少犯了两个错误。第一个是@Alarid说的：你不应该破坏你的阵列。第二种情况是，在运行准备好的语句时，必须将DoctrineDBALTypes Conversion用于IN clause。
最后你的问题是：
$stmt = $this->getDoctrine()->getEntityManager()
->getConnection()
->prepare('SELECT t1.id , t1.name , t2.start_date , t2.end_date
FROM table1 t1 , table2 t2
WHERE t1.id = t2.matchId AND t1.id IN (:ids)');
$stmt->bindValue('ids', $idSArray, 'Doctrine'DBAL'Connection::PARAM_INT_ARRAY);
$stmt->execute();
或替代方案：
$stmt = $this->getDoctrine()->getEntityManager()
->getConnection()
->executeQuery('SELECT t1.id , t1.name , t2.start_date , t2.end_date
FROM table1 t1 , table2 t2
WHERE t1.id = t2.matchId AND t1.id IN (:ids)',
array('ids' => $idSArray),
array('ids' => 'Doctrine'DBAL'Connection::PARAM_INT_ARRAY)
)
;
@Hast您发布的第二个代码块有效。正在添加新答案，以便将来的观众可以参考。。。
$ids = [1,2];
$sql = "SELECT t1.id , t1.name , t2.start_date , t2.end_date
FROM table1 t1 , table2 t2
WHERE t1.id = t2.matchId AND t1.id IN (?)";
$stmt = $this->getEntityManager()->getConnection()->executeQuery(
$sql,
[$ids],
['Doctrine'DBAL'Connection::PARAM_INT_ARRAY] // for an array of strings use PARAM_STR_ARRAY
);
$data = $stmt->fetchAll();
你必须让你的数组成为一个数组，不要让它内爆。
$params = array(
'ids' => $idSArray
);