Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi,
In my app, I have users, events and groups. The groups contain several events through a hasMany relationship (the events table has a group_id linking them). My users are assigned roles in the events through a pivot table user_event_roles. Sometimes a user is part of an event, but not others in the group. I'd like to query for all the groups, where my user is in at least one event and get it as something like [club_id: 1, events: [...]].
ChatGPT recommended I put the following in my user model.
public function groups_with_events()
{
return Group::whereHas('events', function ($query) {
$query->whereIn('id', $this->events()->pluck('events.id'));
})->with([
'events' => function ($query) {
$query->leftJoin('user_event_roles', function ($join) {
$join->on('events.id', '=', 'user_event_roles.event_id')
->where('user_event_roles.user_id', '=', $this->id);
})
->leftJoin('event_roles', 'user_event_roles.role_id', '=', 'event_roles.id')
->select(
'events.*',
'event_roles.role as role' // Fetch the role name
);
}
])
->get();
}
and then call $user->groups_with_events(); in my controller.
This works in that it gives me the desired output, but it feels wrong. (because it is) How do I do this properly? How can I define this relationship, to avoid clunky queries like above?
I know this is not the way to go, but I'm really trying to learn and improve. Any pointers are appreciated!
So here's something I tried. In my user model:
public function groups(): hasManyThrough
{
return $this->hasManyThrough(Group::class, Event::class, 'group_id', 'id');
}
Then I can call $user->groups()->with('events')->get(); and get the groups with events[] as an attribute.
This seems better to me. How do I get the roles attached to this in an elegant way?
Really trying to figure out the laravel way of doing this instead of using some clunky query.
Here's the simplest way I could do this so far. I'm still very much interest in learning how to do it more elegantly.
Group::with(['events' => function ($query) use ($user_id) {
$query->leftJoin('user_event_roles', function ($join) use ($user_id) {
$join->on('events.id', '=', 'user_event_roles.event_id')
->join('event_roles', 'user_event_roles.role_id', '=', 'event_roles.id')
->where('user_event_roles.user_id', '=', $user_id);
})->select('events.*', 'event_roles.role as role');
}
])->get();
@jak888 you say you want to filter groups but this query will load all of them, and than some will have no events matching your condition
Please or to participate in this conversation.