Symfony4 - Doctrine 跨数据库连接配置 [英] Symfony4 - Doctrine Cross Database joins configuration
问题描述
我需要跨数据库关系，我阅读有关此内容，但由于映射问题，我无法获得我想要的内容.这是我的情况
I need cross database relations, i've read about this buti can't get what i want due to a mapping issue. This is my situation
namespace App\Entity\Utility;
use Doctrine\ORM\Mapping as ORM;
use App\Entity\Crm\User;
/**
* Description of Test
*
* @ORM\Table(name="fgel_utility.fgel_test")
* @ORM\Entity(repositoryClass="App\Repository\Utility\TestRepository")
*/
class Test
{
/**
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
*
* @var User
*
* @ORM\ManyToOne(targetEntity="App\Entity\Crm\User")
* @ORM\JoinColumn(name="user_cod", referencedColumnName="AUCUT")
*/
protected $user = null;
public function getId()
{
return $this->id;
}
public function getUser(): User
{
return $this->user;
}
public function setId($id)
{
$this->id = $id;
return $this;
}
public function setUser(User $user)
{
$this->user = $utente;
return $this;
}
}
namespace App\Entity\Crm;
use Doctrine\ORM\Mapping as ORM;
/**
*
* @ORM\Table(name="crm.USER")
* @ORM\Entity(repositoryClass="App\Repository\FintelGasDati\AnuteRepository")
*/
class User
{
/**
*
* @ORM\Id
* @ORM\Column(name="AUCUT", type="integer", nullable=false)
*/
protected $codiceCliente;
# SOME CODE
}
我的学说.yaml
doctrine:
orm:
default_entity_manager: default
entity_managers:
#################################
# Update schema only with this em
#################################
default:
connection: mssql_1
mappings:
Utility:
type: "annotation"
# The directory for entity (relative to bundle path)
dir: '%kernel.project_dir%/src/Entity/Utility'
prefix: 'App\Entity\Utility'
alias: Utility
mssql_crm:
connection: mssql_1
mappings:
Crm:
type: "annotation"
# The directory for entity (relative to bundle path)
dir: '%kernel.project_dir%/src/Entity/Crm'
prefix: 'App\Entity\Crm'
alias: Crm
所以他们共享相同的连接(但不同的 em).连接的用户有权读/写两个数据库(但只能更改 fgel_utility 数据库的架构.两个数据库都存储在 SQL Server 2008 中.当我试图执行
So they are sharing the same connection (but a different em). The user of the connections has the privileges to read/write both databases (but only to alter schema to the fgel_utility DB. Both DB are stored in a SQL Server 2008. When i'm tryin' to execute
php bin/console doctrine:schema:update --dump-sql
我收到此错误
在配置的链中找不到类App\Entity\Crm\User"命名空间 App\Entity\Utility, FOS\UserBundle\Model
The class 'App\Entity\Crm\User' was not found in the chain configured namespaces App\Entity\Utility, FOS\UserBundle\Model
推荐答案
您实际上可以欺骗 Doctrine 对 MySQL/MariaDB 进行跨数据库连接查询，只需在实体的 ORM\Table 注释中添加数据库名称前缀即可:
You can actually trick Doctrine to do cross-database join queries to MySQL/MariaDB, simply by prefixing the database name in the ORM\Table annotation of your entites :
// src/Entity/User.php
@ORM\Table(name="dbname.users")
Doctrine 将在所有 SELECT、JOIN 语句中使用它.
This will be used by Doctrine in all the SELECT, JOIN statements.
也就是说，使用此解决方案，将不会使用来自 DATABASE_URL 的 DB_NAME 或您的 env 文件的任何其他值，这可能会导致混淆(因为数据库名称应该与连接耦合，而不是实体).
That beeing said, using this solution, the DB_NAME from DATABASE_URL or any other values of your env files won't be used, which can lead to confusions (as the database name should be coupled to the connection, not the entity).
由于您无法解析 ORM 映射中的动态值，例如@ORM\Table(name=%env(DBNAME)%.users")，但这里是如何使用 LoadClassMetadata 的示例 来自 Doctrine 的事件来动态地完成这项工作.
As you cannot resolve dynamic value in your ORM mappings, such as "@ORM\Table(name=%env(DBNAME)%.users"), but here is an exemple of how you can use the LoadClassMetadata event from Doctrine to do that job dynamically.
类构造函数将 Entities 命名空间作为第一个参数，将数据库名称作为第二个参数.
The class constructor takes the Entities namespace as a first argument, and the database name as the second argument.
当 Doctrine 运行元数据加载时，它将使用每个实体的元数据类触发回调方法，您可以根据这些值动态处理和设置表名.
When Doctrine runs the metadata loading, it will fire the callback method with the metadata class for each entity, onto which you can process and set the table name dynamically from theses values.
// src/DatabasePrefixer.php
class DatabasePrefixer implements EventSubscriber
{
private $namespace;
private $tablePrefix;
/**
* @param $namespace string The fully qualified entity namespace to add the prefix
* @param $tablePrefix string The prefix
*/
public function __construct($namespace, $tablePrefix)
{
$this->namespace = $namespace;
$this->tablePrefix = $tablePrefix;
}
public function getSubscribedEvents()
{
return ['loadClassMetadata'];
}
public function loadClassMetadata(LoadClassMetadataEventArgs $eventArgs)
{
$classMetadata = $eventArgs->getClassMetadata();
if ($this->namespace == $classMetadata->namespace) {
$classMetadata->setTableName(sprintf('%s.%s', $this->tablePrefix, $classMetadata->table['name']));
}
}
}
假设你有一个 DB_NAME 环境变量，在你的 config/services.yml 中将该类配置为一个服务，使用 Symfony 的 yaml 解析功能，以及事件标记来监听正确的 Doctrine 事件:
Supposing that you have a DB_NAME env variable, configure the class as a service in your config/services.yml, using the yaml resolving features of Symfony, and the event tagging to listen to the correct Doctrine event :
// config/services.yaml
services:
[...]
dbname.prefixer:
class: App\DatabasePrefixer
arguments:
$namespace: 'App\Entity'
$tablePrefix: '%env(DB_NAME)%'
tags:
- { name: doctrine.event_listener, event: loadClassMetadata, lazy: true }
这篇关于Symfony4 - Doctrine 跨数据库连接配置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
