Combine groupBy and withCount query

Posted 6 months ago by faroutchris

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.

Please sign in or create an account to participate in this conversation.