Doctrine executeQuery parameter conversion with named parameters

A problem that I faced in some cases during my work was when I tried to provide parameters to the Connection::executeQuery(), this can be tricky.

Especially if you want to add an array to the parameter list.

My experience is that Doctrine doesn’t recognize the parameter type automatically when we provide an array, other types are recognized automatically. So we always need to define it manually.

You can read more in the documentation about the parameter conversion here.

However, the documentation does not mention the following things:

1. How should we determine the type only for the array parameter when it is not the first one among the parameters?
2. How can we avoid the ‘?’ parameter placeholder in our SQL query?

So, here is an example, it can solve these problems:

// ...

use use Doctrine\DBAL\Connection;

// ...

$connection = $this->getEntityManager()->getConnection();

$statement = $connection->executeQuery(
    'SELECT title, description 
     FROM articles 
     WHERE status = :status
     AND tags IN (:tags)',
    [
       'status' => 'active',
       'tags' => ['PHP', 'Symfony', 'Doctrine']
    ],
    [
       'tags' => Connection::PARAM_STR_ARRAY
    ],
)

// ...

As you can see you can use named parameters here as well such as in repository methods. And with named parameters, you need to specify just the array type in the types argument.

This can be helpful if you have a long parameter list for your query.

Has it helped for you? If yes, leave a comment for me. Or tell me your thoughts about this article.