Symfony4 - Doctrine Cross Database joins configuration

Question

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
}

My doctrine.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

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

I get this error

The class 'App\Entity\Crm\User' was not found in the chain configured namespaces App\Entity\Utility, FOS\UserBundle\Model

Answer 1

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")

This will be used by Doctrine in all the SELECT, JOIN statements.

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).

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.

The class constructor takes the Entities namespace as a first argument, and the database name as the second argument.

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']));
        }
    }
}

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 }

Answer 2

According to this https://github.com/doctrine/doctrine2/issues/6350 cross database joins between different entity managers (same connections) isn't supported.