vendredi 20 mai 2016

Handling many to many relationship through a pivot table

I asked a similar question earlier but have made a lot of changes since then and I have a different issue now. I have changed my schemas to be like the following.

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('userName')->default('');
    $table->string('userEmail')->default('');
    $table->timestamps();
});

Schema::create('user_groups', function(Blueprint $table)
{
    $table->increments('id');
    $table->string('groupName')->default('');
    $table->timestamps();
    $table->softDeletes();
});

Schema::create('users_user_groups', function(Blueprint $table)
{
    $table->increments('id');
    $table->integer('user_id')->unsigned();
    $table->integer('group_id')->unsigned();
});

Schema::table('profusion_users_user_groups', function(Blueprint $table) {
    $table->foreign('user_id')->references('id')->on('users');
    $table->foreign('group_id')->references('id')->on('user_groups');
});

Essentially, a user can be apart of many user_groups, and user_groups can have many users. So I am looking at a many to many relationship which is why I added the table users_user_groups. I then set up my Models for these two like so

class User extends Model
{
    protected $table = 'users';
    protected $guarded = [];

    public function groups()
    {
        return $this->belongsToMany('App\Group', 'users_user_groups')->withPivot('user_id', 'group_id');
    }
}

class Group extends Model
{
    protected $table = 'user_groups';
    protected $guarded = [];

    public function profusionUser()
    {
        return $this->belongsToMany('App\User', 'pusers_user_groups')->withPivot('user_id', 'group_id');
    }
}

I am pretty confident the above is set up correctly, to me it makes sense. Now the work for this takes place in my UserController. The core function is this and I have tried to comment it to explain what is happening.

public function updateUsers()
{
    $users = Helper::returnUsersFromLdap();  //Get all users from Directory

    foreach($users as $userName => $userData) {
        $user = User::firstOrNew(['userName' => $userName]);  //Create user if needed

        foreach ($userData as $userEmail => $userDepartment) {
            $name = preg_replace('/@.*?$/', '', $userEmail);
            $user->userEmail = $userEmail;

            $userGroups = Helper::returnGroupsFromLdap($name); //Get all user_groups user is apart off

            foreach ($userGroups as $group) {
                $usGroup = new Group();  //Create the user_groups
                $usGroup->groupName = $group;
                $usGroup->save();
            }
            $user->save();
            $user->groups()->sync($userGroups);  //Link the users groups to the user
        }
    }

    return Redirect::route('users.index');
}

So my user_groups table is populated with data like so

+-----+------------+---------------------+---------------------+------------+
| id  | groupName  | created_at          | updated_at          | deleted_at |
+-----+------------+---------------------+---------------------+------------+
|   1 | Group 1    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   2 | Group 2    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   3 | Group 3    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   4 | Group 4    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   5 | Group 1    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   6 | Group 3    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   7 | Group 2    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   8 | Group 5    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|   9 | Group 1    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|  10 | Group 2    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|  11 | Group 1    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |
|  12 | Group 3    | 2016-05-20 15:55:34 | 2016-05-20 15:55:34 | NULL       |

Now the first problem is that groups are being repeated in this table when I am not sure if they need to be? If 2 users are in the same group, two entries will be made above, one for each user. Should I make this unique somehow?

Now my second problem is regarding my pivot table. The output of this is like so

+-----+--------------------+
| id  | user_id | group_id |
+-----+--------------------+
|   1 |       1 |        0 |
|   2 |       1 |        0 |
|   3 |       1 |        0 |
|   4 |       2 |        0 |
|   5 |       2 |        0 |
|   6 |       3 |        0 |
|   7 |       3 |        0 |
|   8 |       3 |        0 |
|   9 |       3 |        0 |

So the user_id is being updated properly, and the correct number of times. So I know that the user with the id 1 is in 3 groups. However, for some reason, the group_id is not being updated.

Is there any reason this is not being updated properly? Any suggestions appreciated and it would be good to get some input regarding the user_groups table and whether this should contain unique group names.

Many thanks



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire