[SOLVED] Doctrine DBAL - WHERE IN array with additional parameter

Doctrine DBAL - WHERE IN array with additional parameter

Using the example docs, I was able to get a query like this to work.

SELECT
f.foo,
b.bar
FROM Foo f
LEFT JOIN Bar b
WHERE 
f.foo = 20
AND b.bar IN ?

Using DBAL, this code returns results.

$result = $this->connection->executeQuery(
            $SQL,
            array($bar_array),
            array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
        )->fetchAll();

I would like to search for f.foo as a single integer parameter well as looking for the IN statement, but I haven't figured out how to get it to function, since all of the example documents have the array as the only parameter.

Solution

The params and types are parallel arrays. All you should have to do is add your placeholder for the f.foo value and add in the correct PDO type in types argument.

$SQL = "SELECT f.foo, b.bar
        FROM Foo f LEFT JOIN Bar b
        WHERE  f.foo = ? AND b.bar IN (?)";

$result = $this->connection
    ->executeQuery($SQL, array($foo, $bar_array),array(
            \PDO::PARAM_INT,
            \Doctrine\DBAL\ArrayParameterType::INTEGER
     ))->fetchAll();

You can read the manual for more information.