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:
Hoping that this post will be useful …