The question is, is there a way to specify a many-to-many relationship between models in different databases.
Background: I have a classic case of many-to-many relationship between User and Role models. User has many Roles, and Role has many Users. There is an intermediary table, UserRole, which joins the first two.
In User model, I have:
$this->hasManyToMany("id", "UserRole", "userId", "roleId", "Role", array("alias" => "roles");
In Roles model, I have:
$this->hasManyToMany("id", "UserRole", "roleId", "userId", "User", array("alias" => "users");
And in UserRole model, I have:
$this->belongsTo("userId", "User", "id", array("alias" => "user"));
$this->belongsTo("roleId", "Role", "id", array("alias" => "role"));
Now, the caveat: the Role model's table is in a DIFFERENT DATABASE (different physical computer) from the other two. When I try to get the user's roles, Phalcon tries to execute a query which joins Role to UserRole:
SELECT Role.id, Role.name FROM Role INNER JOIN UserRole ON UserRole.roleId = Role.id WHERE UserRole.userId = :0
If they were in the same database, it would have worked, however, with tables in different databases it doesn't. I can solve this particular problem by instantiating the UserRole models and fetching the roles one by one, but I am looking for a way to define the relationship and have Phalcon manage it by executing separate queries against each of the databases.
Is there such way?
I am not asking for general design suggestions, such as moving the Role table with the others. My case is rather more complex, involving 800 tables in several database servers, and the example above is one I built for the purpose of explaining my problem; the actual tables are different, and I have reasons to keep the tables the way they are.