Doctrine DBAL - WHERE IN array with additional parameter

Question

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.

olivieradam666 · Accepted Answer · 2024-02-16 20:42:44Z

46

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.

edited Feb 16, 2024 at 20:42

olivieradam666

4,7002 gold badges22 silver badges26 bronze badges

answered May 8, 2014 at 18:31

Mathew Peterson

6216 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pstryk Over a year ago

If you read this around 2024, replace \Doctrine\DBAL\Connection::PARAM_INT_ARRAY with \Doctrine\DBAL\ArrayParameterType::INTEGER. At least that's how it works in doctrine/dbal:3.7.1, but I didn't check in which version exactly they changed it.

Etienne Bruines · Accepted Answer · 2018-11-28 11:54:44Z

34

I just encountered the below behavior, so I am writing it here, maybe it will prove useful for someone else.

Beware when using named parameters with IN. You will have to repeat the names for the type-array as well:

$sql = "SELECT * FROM myTable WHERE name IN (:param_array)";

$stmt = $this->db->executeQuery(
        $sql,
        [
            'param_array' => $paramArray
        ],
        [
            'param_array' => Connection::PARAM_STR_ARRAY
        ]
    );

edited Nov 28, 2018 at 11:54

Etienne Bruines

3,0683 gold badges21 silver badges26 bronze badges

answered Apr 19, 2016 at 7:04

George Olah

6276 silver badges16 bronze badges

2 Comments

inanimatt Over a year ago

The above thankfully works fine now, since at least DBAL 2.6 (possibly earlier)

plamen Over a year ago

Thanx, man! I spend 3 hours trying to achieve this. whit no success.

Collectives™ on Stack Overflow

Doctrine DBAL - WHERE IN array with additional parameter

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related