Using Relationships with Multiple Entity Managers

Question

I am wondering if it is possible to create a relationship between two entities that reside in separate databases.

For example if we took the solution found here http://symfony.com/doc/current/cookbook/doctrine/multiple_entity_managers.html and created a one to many relationship with Users in the customer database to Posts in the default database.

Is this something that is supported by Symfony2 and Doctrine?

score 53 · Accepted Answer · answered Jul 15 '12 at 18:35

53

Using different object managers (entity managers) doesn't allow the object graphs to intersect. That case is too complex and isn't managed by Doctrine ORM.

If you need such a case, keep the object graphs disconnected by saving the identifiers of the related objects (old style) instead of a reference to them, then manually get the objects through services. You can find a fairly good example of how this would work in an example of connection between Doctrine2 ORM and Doctrine2 MongoDB ODM. Alternatively, you could also use a @PostLoad event listener that populates data in your entities by creating the link through the repositories I've linked in the example. Same for @PostPersist (which should instead extract the identifiers for the related objects), but beware that this technique can become really messy.

Also, if your RDBMS supports cross-database operations on a single host, you can just use a single EntityManager and reference the other table with @ORM\Table(name="schemaname.tablename").

answered Jul 15 '12 at 18:35

Ocramius

25,171
7
103
107

11

*if your RDBMS supports cross-database operations on a single host, you can just use a single EntityManager and reference the other table*. Excellent advice. Thanks! – noisebleed Dec 24 '12 at 15:17
4

Found out that Doctrine only detects schema changes in the `default_connection` tables, when using only one entity manager and multiple databases. Any idea to workaround this? – noisebleed Dec 25 '12 at 23:26
1

@noisebleed from the docs: $ php app/console doctrine:schema:update --force --em=customer. read more here: http://symfony.com/doc/current/cookbook/doctrine/multiple_entity_managers.html – xfscrypt Aug 10 '15 at 13:57
Hi Ocramius. This answer is pretty old. Are still the facts the way you explained? I was wondering if there's a solution to perform cross-database operations in a "cross-host way". I suppose that's a difficult situation to handle as well could lead to performance issues. – DonCallisto Apr 11 '16 at 16:09
2

@DonCallisto there is no newer approach, as far as I know. – Ocramius Apr 12 '16 at 11:48
@Ocramius about: <> can I set that "schemaname" as a config parameter somewhere? – Francesco Borzi Apr 25 '16 at 14:06
1

Yes, the "Table" annotation has a "schema" attribute since a few minor version of the ORM – Ocramius Apr 30 '16 at 03:24
Like always, doctrine is super limited. – jcarlosweb Mar 01 '19 at 22:48
1

It's not a limitation, it is a design decision inherent from the `UnitOfWork` pattern. Separate instances of it are unrelated and work with disjoint datasets. – Ocramius Mar 02 '19 at 23:22
@jcarlosweb Doctrine is not limited, its that Doctrine allows us to use multiple drivers and cannot always implement everything because the limitations stem from when they aren't all able to perform the same way. Take for example creating custom DQL functions, which is a design decision by the developer, who should document these in their own project – Prof May 05 '19 at 14:02
I know this is old. But I have MariaDB-10.4.6 installed. I set `@ORM\Table(name="itilizate", schema="katkomes")` and `@ORM\Table(name="katkomes.itilizate")` but doctrine only creates the other tables and omit `itilizate` in the default database also it does not exists in the `katkomes` I created. – user3502626 Aug 10 '19 at 23:22

Mortimer · Answer 2 · 2023-04-01T14:08:29.467

This is very old question, but it is still linked by Symfony (now 6.2), so I will give it quick update as this cost me about month of fighting with two entities due to its impreciseness.

The solution to all my problems was this little thing:

#[ORM\Table(schema: 'name_of_database')]

add this attribute to your entity and voilà - it will now use this database name when generating SQL query for this entity. But this is basically hardcoding the name of database for this entity - it won't change in different environments (for example on tests or different setup).

To fix this I've added new listener to Doctrine event loadClassMetadata, which changes schema for selected entities when env variable is changes to test:

namespace App\EventListener;

use Doctrine\Bundle\DoctrineBundle\Attribute\AsDoctrineListener;
use Doctrine\ORM\Event\LoadClassMetadataEventArgs;
use Doctrine\ORM\Events;

#[AsDoctrineListener(event: Events::loadClassMetadata, priority: 500)]
class DoctrineListener
{
    public function loadClassMetadata(LoadClassMetadataEventArgs $event): void
    {
        if ('test' !== ($_ENV['APP_ENV'] ?? false)) {
            return;
        }

        $meta = $event->getClassMetadata();
        if (property_exists($meta, 'table') && isset($meta->table['schema'])) {
            $meta->table['schema'] .= '_test';
        }

        if (!property_exists($meta, 'associationMappings')) {
            return;
        }

        // ManyToMany tables
        foreach ($meta->associationMappings as $i => $associationMapping) {
            if (!isset($associationMapping['joinTable']['schema'])) {
                continue;
            }

            $meta->associationMappings[$i]['joinTable']['schema'] .= '_test';
        }
    }
}

With this you could also have dynamic schema - change per setup if needed. Hope it helps.

Using Relationships with Multiple Entity Managers

2 Answers2

Linked