How do you setup relationships with DMZ using 3 data tables and a single join table?

[eluser]lilpine[/eluser]
Hi, I was wondering if anyone ever tried this and how it would be implemented. Basically I have the following db structure:

Code:

[roles]      -> [roles_users]        <- [users]
[privileges] -> [actions_privileges] <- [actions]
[modules]    -> [modules_sections]   <- [sections]

Working with these relationships in DMZ is pretty simple, no issues there.

Code:

//Using the standard
class Role extends DataMapper {
    var $has_many = array('user')
}

class User extends DataMapper {
    var $has_many = array('role')
}

However, three tables, (roles, privileges, modules) are linked to each other using a single join table (permissions):

Code:

permissions [id, role_id, privilege_id, module_id]

I'm unable to get these relationships defined in DMZ. There might be a simple solution but for the life of me I can't seem to get it.

[eluser]WanWizard[/eluser]
If you're looking at defining a many-to-many between all these tables using a single join table, stop looking, you can't. Simply because the name of the join table is automatically determines based on the two models in the relation. And not three.

It would be possible if you could define the 6 many-to-many relations involved if you could somehow override the name of the join table.

If you want this, please create an issue at http://bitbucket.org/wanwizard/datamapper/issues, so I can have a look at it. I might be able to sqeeze it in for the 1.8.0 release, planned for the end of the year.

[eluser]lilpine[/eluser]
Thanks for the reply Wizard. I was hoping that it was possible, but after analyzing the sql queries generated by DataMapper I realized that it wasn't.

As you stated, if it was possible to override the name of the join table then it would work. And I will create a new issue on bitbucket for this feature.

Just a quick description for anyone else who may be viewing this thread, I'm working on implementing the RBAC (Role-Based Access Control) system using CodeIgniter and wanted implement it with DataMapper ORM as opposed to the regular CI models.

[eluser]WanWizard[/eluser]
As of now, it is.

Code:

class Many_a extends DataMapper {

    var $table = 'many_a';
    var $has_one = array();
    var $has_many = array(
              'many_b' => array(
                      'class' => 'many_b',
                      'join_table' => 'many_links'
              ),
              'many_c' => array(
                      'class' => 'many_c',
                      'join_table' => 'many_links'
              )
        );

Which defines

Code:

many_a => many_links => many_b
many_a => many_links => many_c

where the "many_links" table has the foreign keys "many_a_id", "many_b_id" and "many_c_id".

You can download the latest version from http://bitbucket.org/wanwizard/datamapper/downloads, select 'tip'.

I'll update the website in a minute with the updated docs.

[eluser]lilpine[/eluser]
Thanks, that was really fast. I'll download and give it a try and post the feedback.

[eluser]ricardocasares[/eluser]
How do you save a relation with this kind of setup??

I mean to add a new record to the many_links table:

Code:

id | many_a_id | many_b_id | many_c_id
--------------------------------------
23 | 1         | 2         | 3

[eluser]WanWizard[/eluser]
You save them the same way as any other relationship, the application is not aware that two many-to-many relations are using the same relationship table.

Code:

// link b to a
$a->save($b);
// link c to a
$a->save($c);

You can not save both relations at the same time.

[eluser]sideshowbob[/eluser]
Hi,

Either I am doing something wrong or I have found a bug...

I am trying to do the same as the original poster but using 2 databases.

I have set the join table as below.

Code:

class Users extends DataMapper {

    var $table = 'users';
    var $has_one = array();
    var $has_many = array(
              'roles' => array(
                      'class' => 'roles',
                      'join_table' => 'db2.many_links'
              ),
              'modules' => array(
                      'class' => 'modules',
                      'join_table' => 'db2.many_links'
              )
        );

However the generated query (which throws an sql error) is something like:

Code:

SELECT `users`.*
FROM (`users`)
LEFT OUTER JOIN `db2`.`many_links`
    db2.many_links ON `user`.`id` =
    `db2`.`many_links`.`user_id`
WHERE
    `db2.many_links`.`module_id` = 1

I believe the cause of this error is the period/dot in the alias name. If I replace the period/dot with a underscore then the query works, as below:

Code:

SELECT `users`.*
FROM (`users`)
LEFT OUTER JOIN `db2`.`many_links`
    db2_many_links ON `user`.`id` =
    `db2`.`many_links`.`user_id`
WHERE
    `db2_many_links`.`module_id` = 1

If I add the following line into datamapper.php (around line 4282) then this seems to overcome this issue:

Code:

if ( ($class == $related_field) && ($this->table != $object->table) )
        {
            $object_as = $name_prepend . $object->table;
            $relationship_as = $name_prepend . $relationship_table;
        }
        else
        {
            $object_as = $name_prepend . $related_field . '_' . $object->table;
            $relationship_as = $name_prepend . $related_field . '_' . $relationship_table;
        }
        
/*New Line------->*/$relationship_as = str_replace('.', '_', $relationship_as);
        $other_column = $other_model . '_id';
        $this_column = $this_model . '_id' ;

Has anyone else seen this issue?

Bob

Edit: Example contained typos

[eluser]WanWizard[/eluser]
'join_table' is the name of a table (the join table). So if your 3-relations join table is called 'many_links', specify that.

I don't see why an alias needs to be specified there.

[eluser]sideshowbob[/eluser]
Hi WanWizard,

Thanks for the speedy reply.

Sorry, I made a mistake in my example code but I have corrected the post now.

I was not illustrating the use of 2 databases but hopefully it's clear now.

Btw, this example works fine (without the code hack) if only one database is used.

Bob