Cache issue using Doctrine with multiple Entity Managers

Question

We have the need to split our database to have a LIVE one and an Archive one

For that we created a LIVE database and an Archive one and we did setup doctrine like so :

doctrine:
  dbal:
    default_connection: default
    connections:
      default:
        url: '%env(resolve:RM_DATABASE_URL)%'
        server_version: mariadb-10.3.20
        charset: UTF8
        mapping_types:
          enum: string
      archive:
        url: '%env(resolve:RM_ARCHIVE_DATABASE_URL)%'
        server_version: mariadb-10.3.20
        charset: UTF8
        mapping_types:
          enum: string
  orm:
    default_entity_manager: default
    auto_generate_proxy_classes: "%kernel.debug%"
    entity_managers:
      default:
        connection: default
        naming_strategy: doctrine.orm.naming_strategy.default
        auto_mapping: true
        mappings:
          App:
            is_bundle: false
            type: annotation
            dir: '%kernel.project_dir%/src/Entity'
            prefix: 'App\Entity'
            alias: App
        dql:
          string_functions:
            field: DoctrineExtensions\Query\Mysql\Field
            timestampadd: DoctrineExtensions\Query\Mysql\TimestampAdd
            timediff: DoctrineExtensions\Query\Mysql\TimeDiff
            date_format: DoctrineExtensions\Query\Mysql\DateFormat
            JSON_EXTRACT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonExtract
      archive:
        connection: archive
        naming_strategy: doctrine.orm.naming_strategy.default
        mappings:
          App:
            is_bundle: false
            type: annotation
            dir: '%kernel.project_dir%/src/Entity'
            prefix: 'App\Entity'
            alias: App
        dql:
          string_functions:
            field: DoctrineExtensions\Query\Mysql\Field
            timestampadd: DoctrineExtensions\Query\Mysql\TimestampAdd
            timediff: DoctrineExtensions\Query\Mysql\TimeDiff
            date_format: DoctrineExtensions\Query\Mysql\DateFormat
            JSON_EXTRACT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonExtract
            metadata_cache_driver: null
            query_cache_driver: null

We made a command that migrate data to archive using Statements. Data is correctly moved and the row we try to fetch is in the correct database

We've tried many things to fetch the row from our ARCHIVE database such as refresh, clear, persist, delete cache but still only this seems to work for us (8 is the ID of the item in databse):

$entity = $this->archiveEntityManager->find(MyEntity::class, 8, LockMode::NONE);

But when trying something like this :

$entity = $this->archiveEntityManager->getRepository(MyEntity::class)->findOneBy(['id' => 8]);

It returns nothing.

Here we're using id for the test but we'd like to fetch our entity using findOneBy(['token' => $myToken])

Answer 1

Here's what worked for me :

$item = $this->archiveEntityManager
            ->getRepository(MyEntity::class)
            ->setEntityManager($this->archiveEntityManager)
            ->find(8);

And in the repository :

public function setEntityManager(EntityManagerInterface $entityManager)
{
    $this->_em = $entityManager;

    return $this;
}

Answer 2

Doctrine does not allow you to work with the same entity in different EM. You need to make a duplicate of the entity class and explicitly make repository classes for both entities. And you need to pass the correct EM to the repository constructors (using config). After that, you need to use repositories in the code and not EM:

$myEntity = $this->myEntityRepository->find(MyEntity::class, 8, LockMode::NONE);

$myArchivedEntity = $this->myArchivedEntityRepository->find(MyArchivedEntity::class, 8, LockMode::NONE);