My SQL looks something like this:
$sql = "select * from user where id in (:userId) and status = :status";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue(':userId', $accounts, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
$stmt->bindValue(':status', 'declined');
$stmt->execute();
$result = $stmt->fetchAll();
But it returns:
An exception occurred while executing (...)
with params [[1,2,3,4,5,6,7,8,11,12,13,14], "declined"]
Notice: Array to string conversion
I cannot user queryBuilder because my real SQL is more complicated (ex. contains joined select, unions and so on)
You can't use prepared statements with arrays simply because sql itself does not support arrays. Which is a real shame. Somewhere along the line you actually need to determine if your data contains say three items and emit a IN (?,?,?). The Doctrine ORM entity manager does this for you automatically.
Fortunately, the DBAL has you covered. You just don't use bind or prepare. The manual has an example: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion
In your case it would look something like:
$sql = "select * from user where id in (?) and status = ?";
$values = [$accounts,'declined'];
$types = [Connection::PARAM_INT_ARRAY, \PDO::PARAM_STR];
$stmt = $conn->executeQuery($sql,$values,$types);
$result = $stmt->fetchAll();
The above code is untested but you should get the idea. (Make sure you use Doctrine\DBAL\Connection; for Connection::PARAM_INT_ARRAY)
Note for people using named parameters:
If you are using named parameters (:param instead of ?), you should respect the parameter names when providing types. For example:
$sql = "select * from user where id in (:accounts) and status = :status";
$values = ['accounts' => $accounts, 'status' => 'declined'];
$types = ['accounts' => Connection::PARAM_INT_ARRAY, 'status' => \PDO::PARAM_STR];