Combine groupBy and withCount query
I have two tables that are related in my database, leagues and events. One of my endpoints offers a list of all leagues grouped by category.
I would like to write a method that returns an extra field in the array of leagues to show how many related events exist for each league.
Here's the relevant parts of my models:
class League extends Model
{
public function events()
{
return $this->hasMany(Event);
}
public function getByGroup()
{
return $this->all()->groupBy('group');
}
}
class Event extends Model
{
public function league()
{
return $this->belongsTo(League);
}
}
Here's an example output of what I would like to have:
{
...
"Basketball": [{
"id": 4,
"active": true,
"group": "Basketball",
"details": "US Basketball",
"title": "NBA",
"events_count": 5 // <--- this is what I want
}],
"Football": [{...}, {...}, {...}]
...
}
Currently I am solving this by calling an additional function in my controller that adds up each event. This results in an extreme amount of calls.
I can't seem to figure out how to get this to work. I've tried following some advice on StackOverflow for similar questions and looked at the Laravel documentation but I only seem to either not get my expected output or crash the app.
Do you mean this?
public function getByGroup()
{
return $this->withCount('events')->get()->groupBy('group');
}
Please or to participate in this conversation.