使用IN子句的Symfony2.3原始SQL查询 [英] Symfony2.3 raw sql query with IN Clause
问题描述
我正在尝试使用针对entry子句的entityentitymanager运行原始sql查询,如下所示.
I was trying to a run raw sql query with doctrine entitymanager for IN clause as shown below.
$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条件硬编码为
But I am only getting result for Id = 1. If I hardcode the WHERE IN condition as
WHERE t1.id = t2.matchId AND t1.id IN (1,2)');
然后获得两个ID的结果.谁能告诉我在传递$ params数组时我做错了什么.我还打印了输出1,2的爆破结果.因此,我无法找到错误以及使用IN子句执行原始sql查询的方式.
Then getting result for both the Ids. Can anyone tell me that what I am doing wrong in passing the $params array. I have also print the implode result which outputs 1,2. So I am not able to find the mistake and also the way to perform raw sql query with IN clause.
推荐答案
答案:
所以您至少犯了两个错误.首先是@Alarid所说的:您不应该破坏您的数组.第二个是在运行准备好的语句时必须对IN clause
使用DoctrineDBALTypes Conversion
.
So there are at least two mistakes you did. The first is what @Alarid said: you should not implode your array. The second is that you have to use DoctrineDBALTypes Conversion
for IN clause
when running a prepared statement.
最后您的查询如下:
$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)
)
;
这篇关于使用IN子句的Symfony2.3原始SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!