This is an archived copy of the forum for reference purposes

Problem with joining table in many-to-many relationships

posted by aris 9 years ago

I have a database schema with two tables, 'users' and 'groups', and a joining table, 'user_groups'. The users and groups tables are very straightforward (I put only relevant fields, for briefness):

CREATE TABLE users (
    id              serial PRIMARY KEY,
    uid             varchar(32),
    name            varchar(64),
    surname         varchar(64),
);

CREATE TABLE groups (
    id              serial PRIMARY KEY,
    groupid         varchar(16) DEFAULT '',
    groupname       varchar(128) DEFAULT '',
    active          boolean DEFAULT FALSE,
);

CREATE TABLE users_groups (
    id_user         integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    id_group        integer NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    PRIMARY KEY (id_user, id_group)
);

This schema looks fine and fCore::expose($schema->getRelationships('users')) shows the following for users table:

Array
(
    [one-to-one] => Array
        (
        )
  
    [many-to-one] => Array
        (
        )
  
    [one-to-many] => Array
        (
        )
  
    [many-to-many] => Array
        (
            [0] => Array
                (
                    [table] => users
                    [column] => id
                    [related_table] => groups
                    [related_column] => id
                    [join_table] => users_groups
                    [join_column] => id_user
                    [join_related_column] => id_group
                    [on_update] => no_action
                    [on_delete] => cascade
                )
        
        )
  
)

However, so long as each user could have a distinct role in each group he is member of, I would need to add a 'role' field to the user_groups table:

CREATE TABLE users_groups (
    id_user         integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    id_group        integer NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    role            varchar(16) DEFAULT '',
    PRIMARY KEY (id_user, id_group)
);

The problem is that after adding that new field, the many-to-many relationship is lost:

Array
(
    [one-to-one] => Array
        (
        )
  
    [many-to-one] => Array
        (
        )
  
    [one-to-many] => Array
        (
            [0] => Array
                (
                    [table] => users
                    [column] => id
                    [related_table] => users_groups
                    [related_column] => id_user
                    [on_delete] => cascade
                    [on_update] => no_action
                )
        
        )
  
    [many-to-many] => Array
        (
        )
  
)

I don't see the reason for which it doesn't work, so long as the foreign keys are the same ones and they are single column FOREIGN KEY constraints. Any help?

Trying to circumvent the problem above, I have defined a new 'roles' table, using the joining table primary key pairs as foreign key, as follows:

CREATE TABLE roles (
    id_user         integer NOT NULL,
    id_group        integer NOT NULL,
    role            varchar(32),
    PRIMARY KEY (id_user, id_group),
    FOREIGN KEY (id_user, id_group) REFERENCES users_groups(id_user, id_group) ON DELETE CASCADE
);

This is what I get for that table using getRelationships:

Array
(
    [one-to-one] => Array
        (
        )
  
    [many-to-one] => Array
        (
            [0] => Array
                (
                    [table] => roles
                    [column] => id_user
                    [related_table] => users_groups
                    [related_column] => id_group
                )
        
        )
  
    [one-to-many] => Array
        (
        )
  
    [many-to-many] => Array
        (
        )
  
)

As you see, it identifies 'id_user' as column referencing to 'id_groups' in users_groups, while it should be id_user,id_group in both cases.

If this doesn't work, how could I solve the problem of linking values to the joining table?

Regards, A.

posted by aris 9 years ago

Unfortunately Flourish does not support multi-column foreign keys. The rather extensive additional complexity of modeling multiple columns per relationship was just deemed not worth it. That said, multi-column primary keys are supported.

posted by wbond 9 years ago

For a table to be considered a joining table, and thus creating a many-to-many relationship, it can not contain any information that is not contained in the two tables being joined. This is why your first example acts as you were expecting, but the second table does not. With a many-to-many relationship there is no object to model the joining table since it is just the normalized form of storing relationship info.

With the second example, users is in a one-to-many relationship with users_groups and users_groups is in a many-to-one relationship with groups. You'll need a UsersGroup class to represent the users_groups rows and to be able to retrieve the role information.

That said, there is functionality built into Flourish to make retrieval of the related information in your second example easier. With the one-to-many relationship between users and users_groups, you can use the build action:

$users_groups = $user->buildUsersGroups();
foreach ($users_groups as $users_group) {
    echo $users_group->encodeRole();
}

So here $users_groups is an fRecordSet object. There is also a build method for fRecordSet that creates another fRecordSet from a many-to-one relationship. Thus to get all of the groups a user is in, you can call:

$groups = $user->buildUsersGroups()->buildGroups();

I hope this helps.

posted by wbond 9 years ago

Thanks for your reply!

Using the pure many-to-many model (that is, without roles information) I had no problem to get both related records stored with something as:

$user = new User();
$user->setUid   ('useruid');
$user->setName  ('Someusername');
$user->setEmail ('somemail@mail.com');
$user->store();

$group = new Group();
$group->setGroupid   ('homegroup');
$group->setGroupname ('Full group name');
$group->setType      ('project');

$group->associateUsers(array($user));
$group->store();

But if join tables can't contain extra fields and multi-column foreign keys are neither supported, it seems I will have to follow the three tables route: users, groups, user_groups, and three classes (User, Group, UserGroup). However, using three tables, I'm not able to use associateUsers anymore in user_groups:

$usergroup->associateUsers(array($user));
$usergroup->associateGroups(array($group));
$usergroup->store();

Because it complains about not being a *-to-many relationship, and it's right, of course). I have then attempted the solution you told about to mblarsen in Message 681:

$user = new User();
$user->setUid   ('useruid');
$user->setName  ('Someusername');
$user->setEmail ('somemail@mail.com');
$user->store();

$group = new Group();
$group->setGroupid   ('homegroup');
$group->setGroupname ('Full group name');
$group->setType      ('project');

$usergroup = new UserGroup();
$usergroup->setRole   ('boss');
$usergroup->setIdUser ($user->getId());

$group->associateUserGroups($usergroup);
$group->store(

);

However, only 'users' and 'groups' tables are updated (just like in mblarsen case). but the 'user_groups' remains empty. Surely I could leave away the 'associate' and use the 'manual' way, getting both ids from users and groups, adding them to $usergroup instance and storing that table too, but I would prefer to stay in a shorter and straight path, if possible. Any news about the ticket you opened with this issue?

Finally, I have not found too much documentation about 'build', 'encode', 'associate', and so on. Although 'get' and 'set' are really intuitive, of course, maybe those 'methods' would need further details.

Thanks for a so nice library, and for your kind help!

posted by aris 9 years ago

Sorry, in the post there is a typo error (it lacks the closing parenthesis):

> $group->associateUserGroups($usergroup);
> $group->store(

In my code I really have '$group->store();', of course.

posted by aris 9 years ago

Well, finally I have got it, but I have had to make two changes:

In the database, I have removed the two-columns primary key, replacing it with a serial column, and indexing the main fields.

CREATE TABLE user_groups (
    id              serial PRIMARY KEY,
    id_user         integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    id_group        integer NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    role            varchar(32),
);
CREATE INDEX user_groups_id_user_idx ON user_groups (id_user) ;
CREATE INDEX user_groups_id_group_idx ON user_groups (id_group) ;

Also, I have had to change the last 'store' call as follows:

...
$usergroup = new UserGroup();
$usergroup->setRole ('boss');
$usergroup->setIdUser ($user->getId());

$group->associateUserGroups(array($usergroup));
$group->store();

If I use $group->associateUserGroups($usergroup); it doesn't work. Setting it within an array, it does it.

posted by aris 9 years ago

I tested it and in previous revisions, if you pass with an array, but the original multi-column primary key, it does work. In r819 I changed it so you can also pass a single fActiveRecord object.

posted by wbond 9 years ago

I'm actually going to be looking into the associate issue right now, I'll let you guys know what happens.

In terms of the operations that are possible for column, you should check out fActiveRecord#ColumnOperations. That describes the prepare and encode verbs for columns. The section fActiveRecord#RelatedRecordsOperations describes all of the operations that can be performed on an fActiveRecord for manipulating related records.

The fRecordSet page has all of the documentation for the class, but the build action is described at fRecordSet#FromRelatedRecords.

If you want to learn about the action verbs used throughout Flourish, check out the MethodNaming page.

posted by wbond 9 years ago

There is also a build method for fRecordSet that creates another fRecordSet from a many-to-one relationship. Thus to get all of the groups a user is in, you can call: $groups = $user->buildUsersGroups()->buildGroups();

Yes, that 'build' works. However, it seems it lacks the pagination feature described for 'normal' build method. Then, to get a paginated output of the groups a user is member of, I'll have to use a manual way... Is pagination available on other build methods (i.e. buildFromSQL)?

posted by aris 9 years ago

Yes, the build methods on fActiveRecord will create the whole set of record since it used a cached version of the records when possible, and also uses those cached record when associating records.

You have two choices for build record related to fActiveRecord objects:

Call the full build and use fRecordSet::slice()
Perform an fRecordSet::build() and specify the appropriate foreign key in the where conditions array

In terms of doing pagination in fRecordSet::buildFromSQL(), just use the SQL LIMIT and OFFSET clauses. For fRecordSet::buildFromArray(), use array slice before passing the array in.

posted by wbond 9 years ago