How to manage Doctrine queries with multiple db schemas

Question

I have an entity A with a relation ManyToOne with B but A and B doesn't belong to the same DB schema.

Entity 'A' belongs to MyBundle bundle, and entity 'B' belongs to MyOtherBundle bundle.

The official documentation explain how to work with different connections : multiple schemas = multiple entity manager. But in my case I would like to join both entities.

By doing :

$this->objEm->getRepository('MyBundle:MyEntity')->find($id);

or

$this->objEm->getRepository('MyBundle:MyEntity')->getMyResult($id);

I only call one of my repository, and I guess he's not able to get the other because in my config.yml I can chose only one connection.

doctrine:
  dbal:
   connections:
     connection1:
       driver:   "%database_driver%"
       host:     "%database_host%"
       port:     "%database_port%"
       dbname:   "%database_name%"
       user:     "%database_schema1_user%"
       password: "%database_schema1_password%"
       service:  "%database_service%"
       charset:  "Windows-1252"
     connection2:
       driver:   "%database_driver%"
       host:     "%database_host%"
       port:     "%database_port%"
       dbname:   "%database_name%"
       user:     "%database_schema2_user%"
       password: "%database_schema2_password%"
       service:  "%database_service%"
       charset:  "Windows-1252"

orm:
  entity_managers:
    em1:
      connection:       connection1
      mappings:
              MyBundle: ~
              MyOtherBundle: ~
    em2:
      connection:       connection2
      mappings:
              MyOtherBundle: ~

Result : Whoops, looks like something went wrong.

1/1ReflectionException: Class FQCN\Of\MyBundle\Entity\B does not exist ...

"I know it doesn't exist dude, I want you to look at the good place now : like at FQCN\Of\MyOtherBundle\Entity\B"

How can I force the path to my entity 'B'?

score 3 · Answer 1 · answered Dec 18 '12 at 20:38

3

If your schema are in the same database, then just define the tables for the entities as

Bundle\Entity\Class:
    type: entity
    table: schema.class

(yaml)

You won't need to specify a second connection. When the schema is explicitly indicated, these joins work perfectly in doctrine 2.

If your schema are in different databases, you are in for some misery, because each and every join will query both databases; for multiple record joins each entry will perform a join meaning you do a number of queries proportional to the number of objects in your result set (BAD).

answered Dec 18 '12 at 20:38

Lighthart

3,648
6
28
47

my classes : `namespace FQCN\Of\MyBundle\Entity; /** * @ORM\Table(name="SCHEMA1.TABLE_1") * @ORM\Entity(repositoryClass="FQCN\Of\MyBundle\Repository\ARepository") */ class A { /** * @ORM\ManyToOne(targetEntity="FQCN\Of\MyOtherBundle\Entity\B") * @ORM\JoinColumn(name="...", referencedColumnName="..."), */ private $b; } namespace FQCN\Of\MyOtherBundle\Entity; /** * @ORM\Table(name="SCHEMA2.TABLE_2") * @ORM\Entity(repositoryClass="FQCN\Of\MyOtherBundle\Repository\BRepository") */ class B {}` Dump A => Class FQCN\Of\MyBundle\Entity\B does not exist. – user1913526 Dec 19 '12 at 09:21
Arg sorry I didn't achieve to fix this indentation crap. Anyway I added schema name in my annotations and only use one connection. It still searches my entity B with the wrong FQCN :/ but I'm 100% sure the good ones are defined in annotations. I only get an object with repository ->find(); and then Debug::dump it. That's at this moment that I have the error : Dump A => "Class FQCN\Of\MyBundle\Entity\B does not exist." which is the wrong FQCN. – user1913526 Dec 19 '12 at 09:32

score 2 · Accepted Answer · answered Dec 19 '12 at 16:23

Problem solved ! It had absolutely nothing to do with databases schema nor annotations.

In entity A, one of my personnal setter was forcing type in parameter :

public function setB(B $objB) { //... }

... and I forgot to use B's FQCN ! That's why it was using A's one.

Next time I won't declare FQCN in the annotation to oblige me to use it at the beginning of my class ! :)

How to manage Doctrine queries with multiple db schemas

2 Answers2