Symfony2.3 raw sql query with IN Clause

Question

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

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

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.

possible duplicate of [I have an array of integers, how do I use each one in a mysql query (in php)?](http://stackoverflow.com/q/330268/1503018) — sectus, Jul 11 '13 at 09:26

score 17 · Accepted Answer · edited Feb 14 '14 at 23:10

Answer:

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.

And finally your query goes this:

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

Or alternative:

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

Your first alternative currently fails with a Array to string conversion error. The second alternative works. This is also stated in the manual : The parameter list support only works with Doctrine\DBAL\Connection::executeQuery() and Doctrine\DBAL\Connection::executeUpdate(), NOT with the binding methods of a prepared statement. — 10us, Jan 14 '15 at 15:05

score 1 · Answer 2 · answered Oct 25 '18 at 21:04

@Hast The second code block you posted works. Adding a new answer so future viewers can reference...

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

score 0 · Answer 3 · answered Jul 11 '13 at 09:25

0

You have to let your array as an array, don't implode it.

$params = array(
   'ids'  => $idSArray
);

answered Jul 11 '13 at 09:25

Alarid

770
1
6
19

2

In that case I am gettin error Notice: Array to string conversion in .....\vendor\doctrine\dbal\lib\Doctrine\DBAL\Statement.php line 138 – xyz Jul 11 '13 at 09:27
Can you tell me more about this error ? I can't remember exactly how to do this, but I think you don't need to implode your array. Look at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html, point 4.2.2 – Alarid Jul 11 '13 at 09:29

Symfony2.3 raw sql query with IN Clause

3 Answers3

Linked