Eloquent relations with pivot table?

@FabianH watch this video https://laracasts.com/series/laravel-5-fundamentals/episodes/21

I was working with a very similar model and ended up in a bit of a mess with being able to easily reference the different fields of the pivot table, without wanting to have to write them all over the code and having to always add the extra pivot fields in any queries. It became rather complex with loads of whereHas queries in scopes to be able to properly filter based on various criteria, using withPivot and then having to add $model->pivot->field everywhere... I found it wasn't anywhere near as simple and flexible as a basic set of model relationships when it forms a core part of your application.

I ended up doing what you are suggesting and had an equivalent to your GroupMember. In my case I now have Community, User, Role, Member, with the Member Model acting as the glue between the other 3. This means I can very easily query the Member model to see if certain users are a member of certain groups (Communities) with certain roles, and just eager load the necessary Community or User as and where I will need to use it.

I'd recommend using the pivot table approach for a basic many-many relationship, but as soon as a 3rd model comes into the fray I found it got very complex very quickly, and if you ever wanted to start adding more fields into the pivot table it would end up being a lot of work.

@FabianH Is there a reason to Group Members receive the role_id key?

You could resolve your problem changing the style of your relationships:

You will need 3 models.

1. Group
2. Member
3. Role

And the following relationships 1 - A Group has many members. 2 - A Member has one (or many) role(s).

"I want to query all members from a group with their role in the group"

$group = Group::with('members.role')->first();

#Get the role for a member
foreach($group->users as $user) {
    # Do whatever you want with $user->role (it will be an object with the attributes id, name and leader)
}

Does it helps you?

Thank you @absiddiqueLive, im currently watching this episode, and im now not quite certain if my "group_members" table really is a pivot table, because im not really sure if this there is a many to many relationship involved. "members" should can be part of multiple groups.

@vitorarjol thank you for your reply ! :) I currently have 4 models: Member, Role, Group, GroupMembers. The reason the "role_id" key is in the "group_members" table is that on my frontend i want to loop trough all the users that have a role where the "leaders" = false, and then i want to loop trough and display all the members in this group that have the "leaders" = true. And this is exactly where im getting confused, i don't know if i need a "GroupMember" model, I kinda got it to work with something like GroupMember::with('role', 'member') but that failed if i had more than 13 members per "group_member". I hope you understand where my problem is, maybe the database structure is not right?

@squigg thank you for your reply, you are exactly describing my problem, i probably should rename my table "members" to "users" and only if they are in a "group" they are "members". I just think that eloquent is really elegant, and I can't quite understand why there is no baked in approach to this problem, or some clarification (at least I really did not find anything that helped)

For your loop you can grab from the back end the users.

$users = Role::where('leaders', false)->find(1)->users()->get();

Personally, I don't think that the GroupMember model is necessary.

Maybe you can post some code, will help us to help you :)

@vitorarjol - I forgot two columns in the "group_members" table - the table additionally has a "from" and a "to" timestamp column, that specifies from when to when a person in a group has access to the group

I tried something like this

   $members = GroupMembers::with('roles','members')->where('group_id', $id)->get();

This worked - i got all the members from a group with their roles and the "members" relation - but the weird thing is that, after 12 or 13 items in the collection, I did not get any more relations - the relation field just said "null"..

But again, this approach uses a "GroupMembers" model, thank you for your reply @vitorarjol, i really appreciate your help

Oh! Now I undestand @FabianH.

Remove the role_id from the group_members table, it doesn't need to know the role of the member.

   $members = GroupMembers::with('members.roles')->where('group_id', $id)->get();
   #'members.roles' will load the nested relationship, so you can access the role fields. Try it out and let me know if this works \o

@vitorarjol thank you for your reply! :) but i think i need "role_id" because a user can be in multiple groups, with different role_ids. So for example there is a user "Vitorarjol" - he can be in group "laracasts" with a role of "chief" where he would be a member from "7th july 2015" to "6th july 2019". And later, he could be in a group "laravelfriends" with the role "administrator" from "10th July 2015" to "9th August 2023". so in the "group_members" table there would be 2 entries -

member_id = 1 (Vitorarjol) (foreign key) | group_id = 32 (foreign key) | role_id = 1 (chief) (foreign key) | from = 7th July 2015 | to = 6th July 2019

and

member_id = 1 (Vitorarjol) (foreign key) | group_id = 43 (foreign key) | role_id = 3 (administrator) (foreign key) | from = 10th July 2015 | to = 9th July 2023

and these are my current models

this is the Member Model

 public function groups(){
        return $this->belongsToMany('App\groups');
    }

this is the Group Model

public function subGroups(){
        return $this->hasMany('App\Group', 'parent_id', 'id');
    }
    public function groupMembers(){
        return $this->hasMany('App\Member');
    }

this is the "Role" Model

public function groupMembers(){
        return $this->hasMany('App\Member');
    }
    public function groups(){
        return $this->hasMany('App\Group');
    }

this is the "GroupMember" Model

 public function members(){
        return $this->hasOne('App\Member', 'id', 'person_id');
    }
    public function groups(){
        return $this->belongsToMany('App\Group');
    }
    public function roles(){
        return $this->hasOne('App\Role','id',  'person_id');
    }

I hope I don't confuse you too much,

Hmm, understood! Just a minute.

@vitorarjol

so the "group" data looks something like this

| id | parent_id (foreign key) | name | from | to |

| 1 | null | laracastsroot | 2009 | 2015 |

| 2 | 1 | laracastssubgroup | 2009 | 2012 |

| 3 | 1 | laracastssubgroup1 | 2010 | 2014 |

| 4 | 1 | laracastsubgroup2 | 2013 | 2019 |

| 5 | null | friendsof lara root | 2007 | 2010 |

| 6 | 5 | friendslarasubgroup | 2000 | 2081 |

| 7 | 5 | friendslarasubgrou | 2001 | 2009 |

| 8 | 5 | friendslarasubgroup2 | 2010 | 2013 |

so a group has one parent and, so the - "parent_id" is an "id" in the same table "groups" and the rows with "parent_id" = null are the root elements

(im sorry, im trying to use git flavored markdown to show this as a list, but it's not quite working yet)

Well, I'm afraid that I haven't an ideal solution. Hope it helps:

#Members Model
 groups ->belongsToMany(App\Group, 'group_details', 'group_id', 'member_id')

#Roles Model
groups ->belongsToMany(App\Group, 'group_details', 'group_id', 'role_id')

#Groups Model
members  -> belongsToMany('App\Member', 'group_details', 'group_id', 'member_id')
#The role relationship really bothers me, because I think that should be related just to the user but I don't know how to handle it in your case.
roles -> belongsToMany('App\Role', 'group_details', 'group_id', 'role_id')

Now you have 4 tables, 3 models and the relationships.

Trust me, I've been through all these options already and the interim Model, even though everything in my coding brain says it's not the best way to do it, has proven to be a far far cleaner and easier approach to work with.

In a way it helps implement the domain logic, as you would refer to a user who is part of a group as a member of that group, so why not have a Member model to work with?

@vitorarjol thank you for your help - I am currently trying a version where i renamed "group_members" to "participants" and then i my model for "participants" looks something like this

public function person(){
        return $this->belongsTo('App\Person');
    }
    public function einheiten(){
        return $this->belongsTo('App\Einheit');
    }
    public function position(){
        return $this->hasOne('App\Position','id',  'person_id');
    }

and i retrieve all the participants that belong to a certain group with ->

 $participants = Participants::with('role','member')->where('group_id', $id)->get();

and this works, BUUUUUUUUUT - if i want to display more than 15 or something participants with their relations, the "$participants" object seems to get broken.. I only get the correct relations if the id isnt more than 15, and then the relation is "null"

#relations: array:2 [▼ "role" => null "member" => Member {#263 ▶} ]

that's what i get then but before it gives me the correct relations, is there a limit in laravel, that stops retrieving relations? or am i missing something?

@squigg yes exactly, that's the approach now I'm now following! thanks for your help, but now i have to resolve the issue when retrieving with the query ..

//EDIT:

i fixed this issue -

my participant.php model looks like this

public function person(){
        return $this->belongsTo('App\Person');
    }
    public function einheiten(){
        return $this->belongsTo('App\Einheit');
    }
    public function position(){
        return $this->belongsTo('App\Position'); 
    }

and my Role.php

      public function mitglieder(){
        return $this->hasMany('App\Mitglieder');
    }

thank you everyone for your awesome help, that got me to solve this! thank you!

So the Solution is to not treat tables like this like a pivot table, that's just the wrong mindset in my opinion. Tables like this are just tables like every other, with normal relationships. I think that's what most people (including me) at first get wrong