Symfony2 DBAL array parameters

Few days ago I had to use DBAL statement that will execute SQL IN(somearray) statement.

Considering that I used Doctrine DBAL in this part of code, I had to find the proper way of doing it.
I wrote some basic things about using Doctrine DBAL inside Symfony2 project, but I didn’t ever need this one before until now …

Regularly, when I made statements with parameters, I found easy to use this approach:

//...
 $sql = 'SELECT rel.path, rel.id
         FROM items_to_collections rel
         INNER JOIN items on items.id = rel.item_id
         INNER JOIN statuses s on s.id = rel.status_id
         INNER JOIN item_types it on it.id = items.item_type_id
         WHERE s.status_symbol = ?
         AND it.item_type_symbol = ?
         AND items.item_name = ?';
 
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(1, 'st_live');
        $stmt->bindValue(2, 'it_application');
        $stmt->bindValue(3, $appname);
        $stmt->execute();
        $item = $stmt->fetch(\PDO::FETCH_ASSOC);
 
//...

But, when you try to pass array in this type of statements, it not working this way.

Instead using $conn->prepare;, $stmt->bindValue(….) …, let’s use executeQuery() function of DBAL connection object.

So, when passing array into SQL statements, for example when we are using:

“SELECT something.name FROM something WHERE something.id IN (?)”

I found this approach to be best:

//...
"SELECT DISTINCT tags.id, tags.tag_name
        FROM tags
        INNER JOIN tags_to_items tti on tti.tag_id = tags.id
        WHERE tti.item_id IN (?)",
        array($items_ids), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));
        $tags = $stmt->fetchAll(\PDO::FETCH_ASSOC);
//...

Of course using this way to fetch data, you also have to choose right parameter type which can be one of types: \Doctrine\DBAL\Connection::PARAM_some_param_type or can be one of types:
\PDO::PARAM_something.

Hoping that this post will be useful …

Cheers :-).