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 :-).
1 comment
How would this work if you wanted to add more thanr just the IN clause to you where condition :
WHERE s.status_symbol = ?
AND it.item_type_symbol = ?
AND tti.item_id IN (?)”