php - Symfony2.3 带有 IN 子句的原始 sql 查询

Question

我正在尝试使用 doctrine 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 查询的方法。

Answer 1

回答:

• List of Parameters Conversion
• DoctrineDBALTypes Conversion

所以你至少犯了两个错误。首先是@Alarid 所说的:你不应该破坏你的阵列。第二个是在运行准备好的语句时，您必须对 IN 子句 使用 DoctrineDBALTypes Conversion。

最后你的查询是这样的:

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