Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

Problem updating records in many-to-many relations

posted by aris 9 years ago

I have two tables, 'roles' and 'permissions' linked by a 'role_permissions' joining table, where each role can use a variable number of permissions.

CREATE TABLE roles (
    id            serial PRIMARY KEY,
    rname         varchar(64) NOT NULL,
    description   text,
    ...
);

CREATE TABLE permissions (
    id            serial PRIMARY KEY,
    pname         varchar(64) NOT NULL,
    ...
);

CREATE TABLE role_permissions (
    id_role       integer NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    id_permission integer NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY(id_role, id_permission)
);

I'm trying to update a permissions record changing at the same time (inserting, deleting) any required records in the joining table, so it looks similar to the users & groups sample in the http://flourishlib.com/docs/fActiveRecord page.

The form contains some fields from the Role record and a list of checkboxes for the available permissions (a build from the whole 'permissions' table). I pass to the form a recordset of all available permissions and an array containing the id of those currently associated to this role, so they can be checked. (I have not found a better way to do that.)

#!text/html

<form action="<?php echo fURL::getWithQueryString() ?>" method="post">
    <!-- The main role record -->
    <label>Role</label>
    <input name="rname" type="text" value="<?php echo $role->getRname() ?>" />

    <label>Description</label>
    <textarea name="description"><?php echo $role->getDescription() ?></textarea>

    <!-- The related permissions -->
    <?php foreach ($available_permissions as $ap): ?>
        <label><?php echo $ap->getPname() ?></label>
        <input class="checkbox" type="checkbox" name="permissions::id[]" value="t" <?php if (in_array($ap->getId(), $current_permissions)): ?>checked="" <?php endif; ?> />
    <?php endforeach; ?>
    ...
</form>

The goal here would be to have all changes in the form reflected in both 'roles' and 'role_permissions' tables with just one operation. However, although the changes made in the main Role fields of the form are correctly stored in its table, the 'role_permissions' join table keeps empty time and again, even deleting some records previously inserted "manually" for testing.

Here is the affected code:

// If no role data in session, instantiate a new one
if (fSession::get('role_form_object')) {
    $role = fSession::get('role_form_object');
} else {
    $role = new Role ($id_role) ;
    $current_permissions = RolePermission::getPermissionIdsArray( $id_role );
}
...
try {
    fRequest::validateCSRFToken(fRequest::get('request_token'));

    $role->populate();
    $role->linkPermissions();
    $role->store();

    fMessaging::create('affected', Role::makeURL(), $role->getId());
    fURL::redirect(Role::makeURL());

} catch (fExpectedException $e) {
    ...// Error management here
}
... // Go to display form

Here is the fCore::expose result:

Array
(
    [one-to-one] => Array
        (
        )
  
    [many-to-one] => Array
        (
        )
  
    [one-to-many] => Array
        (
        )
  
    [many-to-many] => Array
        (
            [0] => Array
                (
                    [table] => roles
                    [column] => id
                    [related_table] => permissions
                    [related_column] => id
                    [join_table] => role_permissions
                    [join_column] => id_role
                    [join_related_column] => id_permission
                    [on_update] => no_action
                    [on_delete] => cascade
                )
        
        )
  
)

Array
(
    [one-to-one] => Array
        (
        )
  
    [many-to-one] => Array
        (
        )
  
    [one-to-many] => Array
        (
        )
  
    [many-to-many] => Array
        (
            [0] => Array
                (
                    [table] => permissions
                    [column] => id
                    [related_table] => roles
                    [related_column] => id
                    [join_table] => role_permissions
                    [join_column] => id_permission
                    [join_related_column] => id_role
                    [on_update] => no_action
                    [on_delete] => cascade
                )
        
        )
  
)

What am I missing here?

Thanks in advance,

A.

Hi, it's solved!

I was using 't' as value for those checkboxes (a copy&paste derelict from other forms :-( ). For this reason, the store() action was deleting any permissions previously stores for that role. Changing to value="<?php echo $ap->getId() ?>" to use the id of each available permission, solved the issue.

Anyway, despite of my mistake, I'm really surprised of the cleanliness of code I get even doing a so complex operation.

Thanks again for such a powerful library!

A.

posted by aris 9 years ago