Symfony Doctrine Database Schema relation to different database

I'm having this problem about designing Doctrine database schema. Assum开发者_JS百科e I have 2 databases, A and B.

I've already make database A schema, and now I need to make database B schema. In database B, one of the tables has relation to database A's table. This is the problem, how can I relate B to A?

@Dziamid is half right.

You technically cannot join two tables over to separate database. But you can fake it would any real intervention.

Configure multiple 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

Define the proper 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 you normally would:

// 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 do joins in DQL queries.

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

But you can load relations using from Doctrine_Collections.

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

This works the same as the Doctrine_Query.

You can't have a relation between tables in different databases. If you do, you will end up with a foreign key constraint error. What you can do, however, is to leave a bare relation_id field and manually load related data from another connection. For example:

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 related:

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