User Relationships

I would personally prefer the single pivot table way since it would be faster to query and easier to access. Laravel provides an easy way to access custom pivot attributes, so you can put your role there, for example as a string access_level or something like that. If you would like to introduce custom role models to furthermore complicate your logic, then you can use a custom pivot model and bind a relationship method to it. If you need some examples, I can provide some, but you can find all you need in the laravel documentation here: https://laravel.com/docs/5.8/eloquent-relationships#many-to-many

Thanks for your reply @davidpetrov. If you have some examples already that you can easily share then I would like to see them. The documentation is great, but some additional example would always be helpful at this stage.

Does this kind of idea and migration look like the right kind of direction?

users projects roles user_has_projects

        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

        Schema::create('projects', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->text('description');
            $table->timestamps();
        });

        Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });

    Schema::create('project_has_users', function (Blueprint $table) {
            $table->unsignedInteger('project_id');
            $table->unsignedInteger('user_id');
            $table->unsignedInteger('role_id');

            $table->foreign('project_id')
                ->references('id')
                ->on($tableNames['projects']);

            $table->foreign('user_id')
                ->references('id')
                ->on('users');

            $table->foreign('role_id')
                ->references('id')
                ->on('roles');

            $table->primary(['user_id', 'project_id',  'role_id']);
        });

Yes, your migrations seem just about right. I can only share a couple of notes from my experience:

1. a tricky problem you might encounter depending on your database engine: initially the foreign keys and the primary keys must be of the exact same type and length for the foreign key constraints to work and not throw an error. So you'll want the ids on your users and projects table to also be unsignedIntegers.

2. Another thing you might want to consider is the default naming convention of the pivot tables: project_user, which can spare you a couple of table name specifications.

3. in order to use a custom pivot model, as far as I recall, you need to give the pivot records an id column, so that eloquent can treat them as a model

Then all you'll need in your User model would be a method like the following:

public function projects()
{
    return $this->belongsToMany(App\Project::class)->withPivot('id', 'role_id')->using(App\ProjectUser::class);
}

For more details on the custom pivots, refer to the documentation: https://laravel.com/docs/5.8/eloquent-relationships#defining-custom-intermediate-table-models

Cheers and happy coding! ^^

Thanks again. I have tidied up my migration example following your comments, does this look about right?

        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

        Schema::create('projects', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->text('description');
            $table->timestamps();
        });

        Schema::create('roles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->timestamps();
        });

        Schema::create('project_user', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('project_id');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('role_id');
            $table->timestamps();

            $table->foreign('project_id')->references('id')->on('projects');
            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('role_id')->references('id')->on('roles');
        });

@EVERGREENWEBSYSTEMS - Yeah, everything's fine except I'm not sure about the id columns' type. bigIncrements does indeed generate an unsigned incrementing big integer, but again, idk if that's not going to cause any integrity trouble depending on how picky your DB engine is (InnoDB is quite picky in that regard :D). If it works, then it's fine, but I suspect the difference in length will be a problem.

@davidpetrov - It seems to work well.

Test:

    public function a_user_can_create_a_project()
    {
        $this->actingAs(factory('App\User')->create());

        $this->get('/projects/create')->assertStatus(200);

        $role = factory('App\Role')->create();

        $attributes = [
            'title' => $this->faker->sentence,
            'description' => $this->faker->paragraph,
            'role_id' => $role->id,
        ];

        $this->post('/projects', $attributes)->assertRedirect('/projects');

        $this->assertDatabaseHas('projects', [
            'title' => $attributes['title'],
            'description' => $attributes['description'],
        ]);

        $this->get('/projects')->assertSee($attributes['title']);
    }

Controller:

    public function store(Request $request)
    {
        // validate
        $attributes = request()->validate([
            'title' => 'required',
            'description' => 'required',
        ]);

        // persist
        Project::create($attributes)->users()->attach(auth()->user(), ['role_id' => request('role_id')]);

        // redirect
        return redirect('/projects');
    }

    public function index()
    {
        $projects = auth()->user()->projects;

        return view('projects/index', compact('projects'));
    }

Model:

    public function projects()
    {
        return $this->belongsToMany(Project::class)->withPivot('role_id')->using(ProjectUser::class);
    }

I believe this, Project::create($attributes)->users()->attach(auth()->user(), ['role_id' => request('role_id')]);, is an efficient way to persist the record, I couldn't find any examples of making it shorter whilst still maintaining readability.

Sorry, to re-open this but I have a related question.

With the above mentioned Eloquent model I can access the fields from my roles table using {{ $project->pivot->role->name }}, for example like this,

    @forelse ($projects as $project)
    <li>
        <a href="{{ $project->path() }}">{{ $project->title }} ({{ $project->pivot->role->name }})</a></li>
    </li>
    @endforelse

However, if I just return the data from the Eloquent model as a Json response in order to use it within a Vue component I can no longer access the fields from my roles table. The raw Json response looks like this,

{
"id": 1,
"title": "My First Project",
"description": "Vestibulum facilisis, purus nec pulvinar iaculis, ligula mi congue nunc, vitae euismod ligula urna in dolor. Praesent adipiscing. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Phasellus a est. Quisque libero metus, condimentum nec, tempor a, commodo mollis, magna.\r\n\r\nPellentesque posuere. Suspendisse non nisl sit amet velit hendrerit rutrum. Fusce risus nisl, viverra et, tempor et, pretium in, sapien. In auctor lobortis lacus. Vestibulum volutpat pretium libero.\r\n\r\nVivamus elementum semper nisi. Fusce fermentum odio nec arcu. Vestibulum fringilla pede sit amet augue. Donec id justo. Aliquam eu nunc.\r\n\r\nDonec interdum, metus et hendrerit aliquet, dolor diam sagittis ligula, eget egestas libero turpis vel mi. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Curabitur blandit mollis lacus. Vestibulum turpis sem, aliquet eget, lobortis pellentesque, rutrum eu, nisl. Proin faucibus arcu quis ante.\r\n\r\nIn ut quam vitae odio lacinia tincidunt. Maecenas ullamcorper, dui et placerat feugiat, eros pede varius nisi, condimentum viverra felis nunc et lorem. Proin magna. Vivamus consectetuer hendrerit lacus..",
"created_at": "2019-06-17 21:23:12",
"updated_at": "2019-06-17 21:23:12",
"pivot": {
"user_id": 1,
"project_id": 1,
"role_id": 1
}
},

So, as a result my roles fields are not available. Can anyone help me out here? Is it to do with eager loading?

Thanks

I just stumbled upon a solution that works but I don't fully understand it so if anyone could give me some pointers or point me in the direction of some resource specifically related to this I would be grateful.

If I amend my ProjectUser class to the following:

class ProjectUser extends Pivot
{
    protected $appends = [
        'label',
    ];

    public function getLabelAttribute()
    {
        return $this->role;
    }

    public function role()
    {
        return $this->belongsTo(Role::class);
    }
}

All the fields from the Roles table become available in my Json, like this:

{  
   "id":1,
   "title":"My First Project",
   "description":"Vestibulum facilisis, purus nec pulvinar iaculis, ligula mi congue nunc, vitae euismod ligula urna in dolor. Praesent adipiscing. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Phasellus a est. Quisque libero metus, condimentum nec, tempor a, commodo mollis, magna.\r\n\r\nPellentesque posuere. Suspendisse non nisl sit amet velit hendrerit rutrum. Fusce risus nisl, viverra et, tempor et, pretium in, sapien. In auctor lobortis lacus. Vestibulum volutpat pretium libero.\r\n\r\nVivamus elementum semper nisi. Fusce fermentum odio nec arcu. Vestibulum fringilla pede sit amet augue. Donec id justo. Aliquam eu nunc.\r\n\r\nDonec interdum, metus et hendrerit aliquet, dolor diam sagittis ligula, eget egestas libero turpis vel mi. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Curabitur blandit mollis lacus. Vestibulum turpis sem, aliquet eget, lobortis pellentesque, rutrum eu, nisl. Proin faucibus arcu quis ante.\r\n\r\nIn ut quam vitae odio lacinia tincidunt. Maecenas ullamcorper, dui et placerat feugiat, eros pede varius nisi, condimentum viverra felis nunc et lorem. Proin magna. Vivamus consectetuer hendrerit lacus..",
   "created_at":"2019-06-17 21:23:12",
   "updated_at":"2019-06-17 21:23:12",
   "pivot":{  
      "user_id":1,
      "project_id":1,
      "role_id":1,
      "label":{  
         "id":1,
         "name":"Owner",
         "created_at":null,
         "updated_at":null
      },
      "role":{  
         "id":1,
         "name":"Owner",
         "created_at":null,
         "updated_at":null
      }
   }
},