I'm having this problem about designing Doctrine database schema. Assume 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.
ALTER TABLE item ADD CONSTRAINT item_store_id_store_id FOREIGN KEY (store_id) REFERENCES store(id) NOT DEFERRABLE INITIALLY IMMEDIATE; and database fails to apply it. I think this will only work on mysql MyISAM engine, which doesn't care about constrains.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);
