Doctrine 2与PHP跨数据库关联实现及报错解决方案

Hey there! Let's break down how to replace your old Doctrine 1 multi-connection code and fix that frustrating cross-database association error you're facing.

替代Doctrine 1的多连接配置 #

In Doctrine 1, you registered multiple connections to the global Doctrine_Manager, but Doctrine 2 takes a simpler approach: each EntityManager instance is tied to a single database connection. To replicate your original setup, you just need to create separate EntityManager instances for each database.

Here's how to modify your existing bootstrap code to support two connections:

<?php // bootstrap.php
require_once "vendor/autoload.php";
use Doctrine\ORM\Tools\Setup;
use Doctrine\ORM\EntityManager;

$paths = array("/path/to/entity-files");
$isDevMode = false;
// Reuse the same config for both connections (customize each if needed)
$config = Setup::createAnnotationMetadataConfiguration($paths, $isDevMode);

// Connection for doctrine_test1
$dbParams1 = array(
    'driver' => 'pdo_mysql',
    'user' => 'root',
    'password' => '',
    'dbname' => 'doctrine_test1',
);
$emTest1 = EntityManager::create($dbParams1, $config);

// Connection for doctrine_test2
$dbParams2 = array(
    'driver' => 'pdo_mysql',
    'user' => 'root',
    'password' => '',
    'dbname' => 'doctrine_test2',
);
$emTest2 = EntityManager::create($dbParams2, $config);

// Optional: Store managers in an array for easy access later
$entityManagers = [
    'doctrine_test1' => $emTest1,
    'doctrine_test2' => $emTest2,
];

You can then use the appropriate EntityManager when working with entities from each database.

解决跨库关联查询错误：The target-entity Address cannot be found in 'User#address_id' #

Your scenario (User in database_a, Address in database_b with a cross-database foreign key) needs special mapping handling in Doctrine. Here's how to fix it:

1. Update Entity Annotations to Include Full Table Names #

Doctrine needs explicit direction to find tables across databases. Modify your entity annotations to specify the full database_name.table_name in the @Table attribute:

User Entity (database_a.User)

/**
 * @Entity
 * @Table(name="database_a.User")
 */
class User
{
    /**
     * @Id
     * @GeneratedValue(strategy="AUTO")
     * @Column(type="integer")
     */
    private $id;

    /**
     * @ManyToOne(targetEntity="Address")
     * @JoinColumn(name="address_id", referencedColumnName="id")
     */
    private $address;

    // Getters, setters, and other properties...
}

Address Entity (database_b.Address)

/**
 * @Entity
 * @Table(name="database_b.Address")
 */
class Address
{
    /**
     * @Id
     * @GeneratedValue(strategy="AUTO")
     * @Column(type="integer")
     */
    private $id;

    // Getters, setters, and other properties...
}

2. Use a Single EntityManager with Cross-Database Access #

Since your databases are on the same MySQL instance, you can use one EntityManager connected to a user with permissions for both database_a and database_b. Just point the connection to one of the databases (it doesn't matter which, as long as the user can access both), and Doctrine will resolve cross-database tables via the full table names in your annotations.

Update your connection params like this:

$dbParams = array(
    'driver' => 'pdo_mysql',
    'user' => 'root',
    'password' => '',
    'dbname' => 'database_a', // Connect to one database; user has access to both
);
$entityManager = EntityManager::create($dbParams, $config);

3. Critical Checks to Avoid Issues #

• Verify Database Permissions: Ensure your database user (root in your case) has SELECT, INSERT, UPDATE permissions on both database_a and database_b.
• Clear Metadata Cache: Doctrine caches mapping metadata, so old cached data might cause errors. If using Symfony, run this command:

  php bin/console doctrine:cache:clear-metadata
  

  If not using Symfony, manually delete the metadata cache directory (usually in var/cache/dev/doctrine/orm/Proxies or similar).
• Check Target Entity Namespaces: If your entities use namespaces, make sure the targetEntity in @ManyToOne includes the full namespace (e.g., @ManyToOne(targetEntity="App\Entity\Address")).

What If Databases Are On Different Instances? #

If database_a and database_b are on separate MySQL servers, you can't use Doctrine's association mappings directly. Instead:

• Use separate EntityManagers for each database
• Manually join data via DQL or native SQL queries (fetch Users with address_id, then fetch corresponding Addresses using the second EntityManager)

内容的提问来源于stack exchange，提问作者Ashfaq