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
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.
foo
in app/config/config.yml
.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%
src/Acme/TestBundle/Resources/config/services.yml
.foo_connection
into the service.services:
foo_query_service:
class: Acme\TestBundle\Services\FooQueryService
arguments:
- @doctrine.dbal.foo_connection
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();
}
}
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.
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%
<?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
.
$date = new \DateTime();
$conn = $this->getDoctrine()->getConnection('foo');
$result = $conn->findBarByDate($date);
Hope this helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With