在Symfony2中组织可重用的DBAL查询的最佳做法？ [英] Best practice for organising reusable DBAL queries in Symfony2?

问题描述

目前我正在研究Symfony2项目。大部分是完全标准的;我正在使用ORM层通过我的实体与数据库进行连接。没有问题。

但是，我确实需要对系统中其他位置的现有模式中的少数表进行不经常的查询，其中包含我称之为参考资料：货币换算比例等。我有 SELECT 只能访问此模式。

我设置了另一个连接，我已经下载到DBAL我们的问题是，尽管不常见，我想我将需要重复一些这样的模式，这个模式迄今为止一直在很好的工作。

我的DBAL查询在我应用程序的不止一个地方;我想将这些查询重构为某种存储库，它们更易于使用/测试/等等。我想到了为桌子创建实体，但是我觉得在这种情况下这是太过分了。我是否认为您需要实体创建存储库？

相反，我想知道是否有Symfony方式来执行此操作？东西很好，优雅：）

谢谢！
Darragh

解决方案

更新

2013-10- 03

原谅我编辑一个两岁的答案...但是有几个人对现有的方法提出了质疑，虽然它的工作原理（并且对我的特定用例），定义服务当然是 Symfony方式。

没有人提供了一个例子，为了参考/完整，我会更新我的答案。我不得不承认，当我最初发布了这个答案，但我们生活和学习的时候，我并没有真正地定义自定义服务。

原来的答案保留在下面。

1。创建一个额外的DBAL连接

• 在中创建连接 foo app / config / config.yml 。


• 在这种情况下不需要参数 wrapper_class 答案）

  doctrine：
 dbal： 
连接：
默认值：
驱动程序：％database_driver％
主机：％database_host％
 dbname：％database_name％
用户：％database_user％
 foo：
驱动程序：％foo_driver％
主机：％foo_host％
 dbname：％foo_name％
用户：％foo_user％
  

2。配置服务

• 假设YAML格式。


• 将配置添加到 src / Acme / TestBundle / Resources / config / services.yml 。


• 注意，我们注入上面定义的DBAL foo_connection 进入服务。

  services：
 foo_query_service：
 class：Acme\TestBundle\Services\FooQueryService 
参数：
  -  @ doctrine.dbal.foo_connection 
  pre> 
 
 
 3。为配置的服务创建类
 
 
 
 
 
在 src / Acme / TestBundle / Services / FooQueryService.php创建以下类：
 
 
 
 
 
  php 
 
命名空间Acme\TestBundle\Services; 
 
使用DateTime; 
使用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。最后，在需要他们的地方使用你的查询！
 
 
 
例如，在控制器中... 
  / ** 
 * @Route（/，name =home）
 * @Template（）
 * / 
 public function indexAction（）
 {
 $ date = new \DateTime（）; 
 
 $ result = $ this-> get（'foo_query_service'）
  - > findBarByDate（$ date）; 
 
 return array（）; 
} 
  
完成:)感谢 Acayra 和 koskoz 的反馈。
 
 
 
 
 
 
我想我在这个例子中找到了一个适用于我的解决方案。
 
 
 
我实际上再次看看创建实体/管理器 - 实际上是Symfony2文档，将特定实体映射到多个管理者似乎缺乏。在这种情况下，仍然似乎是一种过分的方法（而且引用模式是相当凌乱的）。
 
 
 
幸运的是，可以为DBAL连接指定一个包装类并将抽象查询转换为具体的方法。
 
 
在 config.yml ：
 
 
 
 
 
  doctrine：
 orm：
连接：
默认值：
驱动程序：％驱动程序％
主机：％主机％
 dbname：％名称％
用户：％用户％
 foo：
 wrapper_class：'Acme\TestBundle\Doctrine\DBAL\FooConnection'
驱动程序：％foo_driver％
主机：％foo_host％
 dbname：％foo_name％
用户：％foo_user％
  
 
 
 
 
 
在指定的路径下创建包装类：
 
 
 
 
 
 <？php 
 
命名空间Acme\TestBundle\Doctrine\DBAL\FooConnection; 
使用Doctrine\DBAL\Connection; 
 
 class FooConnection extends Connection 
 {
 //自定义查询... 
 public function findBarByDate（\DateTime $ date）
 {
 $ stmt = $ this-> prepare（'SELECT * FROM bar WHERE date =：date'）; 
 $ stmt-> bindValue（'date'，$ date，'datetime'）; 
 $ stmt-> execute（）; 
 
 return $ stmt-> fetch（）; 
} 
} 
  
请注意，包装类必须扩展 \Doctrine\DBAL\Connection 。
 
 
在需要的地方使用您的查询： / li> 
 
 
 
 
  $ date = new \DateTime（）; 
 $ conn = $ this-> getDoctrine（） - > getConnection（'foo'）; 
 $ result = $ conn-> findBarByDate（$ date）; 
  
希望这有帮助！
 
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
解决方案
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.

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%



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.

Use your queries wherever you need them:


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

                        
这篇关于在Symfony2中组织可重用的DBAL查询的最佳做法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！