Doctrine: Relations between two entities in two databases

Question

I'm using MySQL and Doctrine 2 (with Symfony 3).

I want to set up a ManyToMany relationship between two entities which are in two separate databases.

I rode that Doctrine doesn't handle this, at least not in an native way. To perform that anyway, I'm using the schema attribute on my tables definitions.

Let's say I have those 2 entities :

/**
 * @ORM\Table(schema="bdd2", name="site")
 */
class Site {}

and

/**
 * @ORM\Table(name="user")
 */
class User {}

I do not use the schema attribute on the User entity because my entity manager is configured to use its database, so it's useless. I set up the relationship in User one this way:

/**
 * @ORM\ManyToMany(targetEntity="[...]")
 */
private $sites;

Quite simple. In the code side, it works, the relation is effective.

In the database side, it's not ok, because the user_site table generated by doctrine has only one foreign key (for the user): it doesn't contain the foreign key to the second database. The field exists, has an index, but not foreign key.

If I add the foreign key by myself, each time I do a php bin/console doctrine:schema:update --dump-sql Doctrine want to remove it.

The question is: How can I make doctrine create this second foreign key? or if it's not possible, How can I make doctrine ignore the one I created myself ?

Answer 1

Here is the solution I suggest, yesterday in my comment I couldn't explain it properly.

When you run doctrine-schema-update (or doctrine-migrations-diff, see https://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html), doctrine does 3 things:

• it generates the schema from mapping informations (e.g. entity annotations), then the postGenerateSchema event is triggered
• it generates the schema from the current db (through reverse engeneering)
• it compares the two generated schemas and generate the SQL code that needs for the second schema to be the same as the first.

So if you manipulate the first schema, you can avoid Doctrine from doing what you don't like.

In this example, I have some entities that are actually VIEWS in the db, not TABLES. So I tell doctrine to don't generate their tables and all the FKs contraints on related table. You can easily start from this working script and adapt to your needs.

Best regards. this is AppBundle/Utils/IgnoreTablesListener.php

<?php


namespace AppBundle\Utils;

use Doctrine\ORM\Tools\Event\GenerateSchemaEventArgs;


class IgnoreTablesListener {
    private $ignoredEntities = null;
    private $ignoredTables = null;


    public function __construct($ignoredEntities) {
        $this->ignoredEntities=$ignoredEntities;
        $this->ignoredTables=array();
    }

    /**
     * Remove ignored entities from Schema
     * This listener is called when the schema as been generated, from entities data mapping (i.e. in doctrine-schema-update or in doctrine:migrations:diff)
     * 
     * @param GenerateSchemaEventArgs $args
     */
    public function postGenerateSchema(GenerateSchemaEventArgs $args)
    {

        $schema = $args->getSchema();
        $em = $args->getEntityManager();

        $ignoredTables = $this->ignoredTables;

        foreach ($this->ignoredEntities as $entityName) {
            $ignoredTables[] = strtolower($em->getClassMetadata($entityName)->getTableName());
        }

        foreach ($schema->getTableNames() as $longTableName) {
            $table=$schema->getTable($longTableName);
            $table_name=strtolower($table->getShortestName($table->getNamespaceName()));

            $fks=$table->getForeignKeys();
            foreach ($fks as $fk) { 
                $foreign_table_name=strtolower($fk->getForeignTableName());

                if (in_array($foreign_table_name, $ignoredTables)) {    //if the fk points to one of the entities i'm ignoring
                    $table->removeForeignKey($fk->getName());   //i remove fk constrains from generated schema (NOT FROM MY CURRENT DB!!!)
//                  dump('removed FK '.$fk->getName().' from '.$table_name.' pointing to '.$foreign_table_name.'.['.implode(', ', $fk->getForeignColumns()).']');
                } 
            }
            if (in_array($table_name, $ignoredTables)) { //if i have to ignore the $table_name table
                $schema->dropTable($longTableName);     //remove the table from generated schema -- NOT FROM DB!!
//              dump('removed ignored table/entity '.$longTableName);
            }

        }

    }

}

and this is the service configuration (app/config/services.yml)

..
app.ignoretableslistener:
    class: AppBundle\Utils\IgnoreTablesListener
    arguments: ['AppBundle:MyEntityToBeIgnoredBecauseItIsAView1', 'AppBundle:MyEntityToBeIgnoredBecauseItIsAView2']
    tags:
        - {name: doctrine.event_listener, event: postGenerateSchema }
..

Answer 2

Thx to @ste answer, there is a solution to let Doctrine create foreign keys to another database.

We have to use the Doctrine event listener feature to inject the foreign key ourself into Doctrine generation schema pipe.

Below my symfony service configuration :

app.services.doctrine_foreign_keys:
    class: AppBundle\Services\DoctrineForeignKeys
    tags:
        - {name: doctrine.event_listener, event: postGenerateSchema }

And the service itself:

namespace AppBundle\EventListener;

use Doctrine\ORM\Tools\Event\GenerateSchemaEventArgs;

/**
 * Class DoctrineForeignKeys
 *
 * @package AppBundle\EventListener
 */
class DoctrineForeignKeys
{
    /**
     * Generate foreign keys to other databases
     *
     * @param GenerateSchemaEventArgs $args
     */
    public function postGenerateSchema(GenerateSchemaEventArgs $args)
    {
        $em = $args->getEntityManager();
        $schema = $args->getSchema();
        $mainSchemaName = $args->getSchema()->getName();

        /**
         * @var \Doctrine\ORM\Mapping\ClassMetadata $metaData
         */
        foreach ($em->getMetadataFactory()->getAllMetadata() as $metaData) {
            $schemaName = $metaData->getSchemaName();
            // this is an entity on another database, we don't want to handle it
            if ($schemaName && $schemaName != $mainSchemaName) {
                continue;
            }

            // fetch all relations of the entity
            foreach ($metaData->associationMappings as $field => $mapping) {
                $targetMetaData = $em->getClassMetadata($mapping['targetEntity']);
                $targetSchemaName = $targetMetaData->getSchemaName();
                // the relation is on the same schema, so no problem here
                if (!$targetSchemaName || $targetSchemaName == $mainSchemaName) {
                    continue;
                }

                if (!empty($mapping['joinTable'])) {
                    foreach ($mapping['joinTable']['inverseJoinColumns'] as $inverseColumn) {
                        $options = array();
                        if (!empty($inverseColumn['onDelete'])) {
                            $options['onDelete'] = $inverseColumn['onDelete'];
                        }
                        // add the foreign key
                        $schema->getTable($mapping['joinTable']['name'])
                            ->addForeignKeyConstraint(
                                $targetSchemaName.'.'.$targetMetaData->getTableName(),
                                [$inverseColumn['name']],
                                [$inverseColumn['referencedColumnName']],
                                $options
                            );
                    }
                } elseif (!empty($mapping['joinColumns'])) {
                    foreach ($mapping['joinColumns'] as $joinColumn) {
                        // add the foreign key
                        $options = array();
                        if (!empty($joinColumn['onDelete'])) {
                            $options['onDelete'] = $joinColumn['onDelete'];
                        }
                        $schema->getTable($metaData->getTableName())
                            ->addForeignKeyConstraint(
                                $targetSchemaName . '.' . $targetMetaData->getTableName(),
                                [$joinColumn['name']],
                                [$joinColumn['referencedColumnName']],
                                $options
                            );
                    }
                }
            }
        }
    }
}