Phalcon Models: Many-To-Many relationship between models in different databases

Question

Phalcon Models: Many-To-Many relationship between models in different databases

stgatev Mar '15

Created Mar '15	Last Reply Mar '15	Replies 1	Views 800	Votes 0

stgatev 2.2k

Mar '15

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.

Thanks,

-Stan

Shad Mickelberry · Answer 1 · 2015-03-04T17:06:14-07:00

Doesn't look like this is possible: https://forum.phalcon.io/discussion/398/splitting-data-over-multiple-databases

Sounds pretty complex and I have no doubt you are more savvy on databases than I am. Just a thought perhaps query caching would be a possibility here.

https://docs.phalcon.io/en/latest/reference/cache.html#querying-the-cache

I know you can't move the table but maybe a query to create a temporary table?

Good luck

stgatev · Answer 2 · 2015-03-04T19:39:27-07:00

Thanks for the suggestion. I don't think a temporary table would work as it will come with its own maintenance requirements which will outweigh any benefits.

I am currently testing the following: explicitly declare the UserRole relation, and fetch the roles by hand:

 class User extends Model {
    public $roles;
    public function initialize() {
       $this->hasMany("id", "UserRole", "userId", array("alias" => "userRoles"));
    }
    public function afterFetch() {
       $roleIds = array_map(function($userRole) { return $userRole["roleId"]; }, $this->userRoles->toArray()); 
       $this->roles = Role::query()->inWhere("id", $roleIds)->execute();
    }
 }

Selecting the roles seems to work. What I am wondering is whether the roles, being a ResultSet object, will integrate seamlessly into the User object.