Loading relationship with condition

Hello,

I have 3 tables : users, groups and members, the members table is the pivot table between users and groups. Furthermore each group belongs to a user (user_id in the groups table).

I need to retrieve the members only for the groups which belong to the authenticated user.

I have this query.

$groups = auth()->user()->groups->load(['members' => function ($query) {
            $query->where('email', '!=', auth()->user()->email);
        }]);

And I don't understand how I can add this condition in my query : load only if the group belongs to the authenticated user.

I had the idea to retrieve 2 collections of groups : first collection with loading members and second collection without loading members.

$groups_1 = auth()->user()->groups->where('user_id', auth()->id())->load(['members' => function ($query) {
            $query->where('email', '!=', auth()->user()->email);
        }]);

$groups_2 = auth()->user()->groups->where('user_id', '!=', auth()->id());

And then merge both collections. But there is perhaps another way to do the same ?

But I think this way doesn't avoid accessing the members in the view (I mean the members who are not loaded).

Do you have any idea how to write this query ?

Thanks a lot ;).

Vincent

aleahy

4 years ago

Level 25

Is the group relationship in the User model defined for the groups that they own, or the ones that they are a member of. You could, and probably should, define both.

/* The groups the user owns */
public function groups()
{
   return $this->hasMany(Group::class);
}

/*The groups the user is a member of */
/* I couldn't think of a better name */
/* This would be the reverse of the members relationship defined in the Groups model */
public function memberGroups() 
{
	return $this->belongsToMany(Group::class, 'members');
}

If you have this, then I don't see why your original query wouldn't work. Calling auth()->user()->groups would then lazily retrieve only the groups that the user owns.

You could lazily eager load the groups and members by calling auth()->user()->load('groups.members').

vincent15000

4 years ago

Level 63

@aleahy Ok but I need both groups : which ones I own and which ones I am member. And I have to load the members only for those I own.

Saying that, I notice that there can be same groups in both collections because I am necessary member of the groups I own.

vincent15000

4 years ago

Level 63

@aleahy I have now this idea ... as I am necessary member of my own groups, I load all groups of which I am member, but I load the other members only if I own the group.

aleahy

4 years ago

Best Answer

Level 25

@vincent15000 If you have the relationships in the user set up the way I mentioned, you would just do

auth()->user()->load(['groups.members', 'memberGroups'])

Then the groups would be in the groups relationship and have loaded a list of members, and the groups they are a member of would be loaded in the memberGroup relationship.

What you have suggested sounds like it would be just one less query, but maybe unnecessarily complicated? But if getting rid of that one query is important, then go for it.

1 like

Please or to participate in this conversation.