Symfony Doctrine Database Schema Relationship to Various Databases

Question

I have a problem with designing a Doctrine database schema. Suppose I have 2 databases, A and B.

I already created a database schema A, and now I need to create a database schema B. In database B, one of the tables is related to the database table A. This is a problem, how can I associate B with A?

Answer 1

You cannot have a relationship between tables in different databases. If you do this, you will get a foreign key constraint error. However, you can leave the bare_id field and manually load the related data from another connection. For instance:

Item: columns: store_id: integer(4) #relations: # Store: # local: store_id # foreign: id # foreignAlias: Items Store: columns: name: string(255) class Item extends BaseItem { protected $_store = null; public function getStore() { if (null == $this->_store) { $this->_store = Doctrine::getTable('Store')->findOneById($this->store_id); } return $this->_store; } public function setStore(Store $store) { $this->store_id = $store->id; } } 

Now you can work with Item and Store, as if they were connected:

 $item = new Item(); $store = new Store(); $store->save(); $item->setStore($store); 

Answer 2

@Dziamid is half right.

Technically, you cannot join two tables to split the database. But you can fake this for any real intervention.

Configure several database connections:

 //databases.yml all: items_db: class: sfDoctrineDatabase param: dsn: mysql://login: passwd@localhost /items stores_db: class: sfDoctrineDatabase param: dsn: mysql://login: passwd@localhost /stores 

Determine the correct connection for each model.

 //schema.yml Item: connection: items_db columns: store_id: integer(4) relations: Store: local: store_id foreign: id foreignAlias: Items Store: connection: stores_db columns: name: string(255) 

Now you can use your Doctrine models as usual:

 // like this $item = new Item(); $store = new Store(); $store->save(); $item->setStore($store); // or like this $item->getStore(); 

The only limitation is that you CANNOT perform joins in DQL queries.

 $query = Doctrine_Query::create() ->from('Store s') ->leftJoin('s.Items i') ->fetchAll(); 

But you can load relationships using Doctrine_Collections.

 $stores = Doctrine::getTable('Store')->findAll(); // this returns a Doctrine_Collection $stores->loadRelated('Items'); 

This works the same as Doctrine_Query.