Doctrine Query Builder and Silex

In my Silex project I use the Doctrine Query Builder from the Database Abstraction Layer package.

"doctrine/dbal": "^2.5"

I register it in my application container like this.

/**
 * Make a connection to the database.
 */
$app['db'] = function() use($app) {
    $connectionParams = [
        'dbname' => $_ENV['DBNAME'],
        'user' => $_ENV['DBUSER'],
        'password' => $_ENV['DBPASS'],
        'host' => $_ENV['DBHOST'],
        'driver' => $_ENV['DBDRIVER'],
    ];

    return \Doctrine\DBAL\DriverManager::getConnection($connectionParams);
};

/**
 * Instantiate the query builder
 */
$app['db.builder'] = function() use($app) {
    return new \Doctrine\DBAL\Query\QueryBuilder($app['db']);
};

When I want to query for database records I am using a repository pattern and then inject the query builder instance into the repository and then I use the query builder in the repository my repositories are created like this.

$app['repository.user'] = function() use($app) {
    return new App\Repositories\UserRepository($app['db.builder']);
};

$app['repository.book'] = function() use($app) {
    return new App\Repositories\BookRepository($app['db.builder']);
};

In the user repository I query for an user to fetch the id and after that I query the book repository for the books that belong to the user, however the problem is that when I use the query builder in the book repository it is already filled with the previous user table. Do I need to reset the query builder instance or am I doing something wrong with registering the query builder into the container.

Solution

QueryBuilder is registered as a usual service and as such only one instance of it will be created. If you happen to populate any user data to it before you query some books, that's the way it is.

To avoid this problem, you might define db.builder as a factory service*. That way, every time you request it, a new instance will be created:

$app['db.builder'] = $app->factory(function () use ($app) {
    return new \Doctrine\DBAL\Query\QueryBuilder($app['db']);
});

Read more about service definitions in Silex at http://silex.sensiolabs.org/doc/master/services.html.

*http://silex.sensiolabs.org/doc/master/services.html#factory-services