Best practice for organising reusable DBAL queries

Question

I'm working on a Symfony2 project at the moment. For the most part it's totally standard; I'm using the ORM layer to interface with the database via my Entities. No problems there.

However, I do need to make infrequent queries to a small handful of tables in an existing schema elsewhere in the system, which contains what I would call 'reference' information: things like currency conversion ratios and such. I have SELECT only access to this schema.

I set up another connection and I have been dropping to the DBAL layer to make the queries on this schema, which has been working pretty well so far.

My issue is that, although infrequent, I think I'll need to repeat some of my DBAL queries in more than one place in my app; I would like to refactor these queries into some sort of repository, where they are more easily used/tested/etc. I thought about creating Entities for the tables, but I feel this is overkill in this case. Am I correct in thinking that you need Entities to create a repository?

Instead I am wondering if there is a 'Symfony way' to do this? Something nice and elegant :)

Thanks! Darragh

Answer 1

Update

2013-10-03

Forgive me for editing a two year old answer... However a couple of people have questioned the existing approach, and while it works (and worked appropriately well for my particular use case), defining services is of course the Symfony way.

Nobody provided an example so, for reference/completeness, I will update my answer. I have to admit I wasn't really au fait with defining custom services when I originally posted this answer, but we live and learn.

The original answer is preserved below.

1. Create an additional DBAL connection

• Create connection foo in app/config/config.yml.
• Argument wrapper_class is not required in this case (see original answer).

doctrine:
    dbal:
        connections:
            default:
                driver:   %database_driver%
                host:     %database_host%
                dbname:   %database_name%
                user:     %database_user%
            foo:
                driver:   %foo_driver%
                host:     %foo_host%
                dbname:   %foo_name%
                user:     %foo_user%

2. Configure service

• Assuming YAML format.
• Add configuration to src/Acme/TestBundle/Resources/config/services.yml.
• Note, we are injecting the above defined DBAL foo_connection into the service.

services:
    foo_query_service:
        class: Acme\TestBundle\Services\FooQueryService
        arguments:
            - @doctrine.dbal.foo_connection

3. Create class for the configured service

• Create the following class at src/Acme/TestBundle/Services/FooQueryService.php:

<?php

namespace Acme\TestBundle\Services;

use DateTime;
use Doctrine\DBAL\Connection;

class FooQueryService
{
    private $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    public function findBarByDate(DateTime $date)
    {
        $stmt = $this->connection->prepare('SELECT * FROM bar WHERE date = :date');
        $stmt->bindValue('date', $date, 'datetime');
        $stmt->execute();

        return $stmt->fetch();
    }
}

4. Finally, use your queries wherever you need them!

For example, in a controller...

/**
 * @Route("/", name="home")
 * @Template()
 */
public function indexAction()
{
    $date = new \DateTime();

    $result = $this->get('foo_query_service')
        ->findBarByDate($date);

    return array();
}

Done :) Thanks to Acayra and koskoz for their feedback.

---

Okay, I think I found a solution that works for me in this instance.

I actually had another look at creating entities/managers - actually the Symfony2 documentation around mapping specific entities to multiple managers seems to be lacking. It still seems like an overkill approach in this instance (and the 'reference' schemas are pretty messy).

Fortunately, it's possible to specify a wrapper class for a DBAL connection and abstract queries into specific methods there.

1. Create an additional DBAL connection with a wrapper class in config.yml:

doctrine:
    orm:
        connections:
            default:
                driver:   %driver%
                host:     %host%
                dbname:   %name%
                user:     %user%
            foo:
                wrapper_class: 'Acme\TestBundle\Doctrine\DBAL\FooConnection'
                driver:   %foo_driver%
                host:     %foo_host%
                dbname:   %foo_name%
                user:     %foo_user%

1. Create the wrapper class at the path specified:

<?php

namespace Acme\TestBundle\Doctrine\DBAL\FooConnection;
use Doctrine\DBAL\Connection;

class FooConnection extends Connection
{
    // custom query...
    public function findBarByDate(\DateTime $date)
    {
        $stmt = $this->prepare('SELECT * FROM bar WHERE date = :date');
        $stmt->bindValue('date', $date, 'datetime');
        $stmt->execute();  

        return $stmt->fetch();
    }
}

Note that the wrapper class must extend \Doctrine\DBAL\Connection.

1. Use your queries wherever you need them:

$date   = new \DateTime();
$conn   = $this->getDoctrine()->getConnection('foo');
$result = $conn->findBarByDate($date);

Hope this helps!

Answer 2

Wow, your answer implies that you have all your queries at the same places for every table.

I don't like this wrapper thing, I prefere having services. One service per table or per bundle, it depends if you have lot of tables and how you'd like to have your queries organized.

Then I pass the desired connection as a service's arguments and that's all.