faroutchris's avatar

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.

0 likes
5 replies
staudenmeir's avatar
Level 24

Do you mean this?

public function getByGroup() 
{
    return $this->withCount('events')->get()->groupBy('group');
}
faroutchris's avatar

Hmm. I tried this before and I get Use of undefined constant Event - assumed 'Event'

Seems I have some other issue in my code then. I can use the Event model directly but it seems it can't be found by Eloquent.

faroutchris's avatar

I figured it out, I am passing in the Event class directly in my hasMany call. It should be a string. Ie, $this->hasMany('Event')

It still doesn't work since it seems that Event is kind of a reserved word in Laravel so I need to rename the model and see if that makes a difference.

Thanks for helping out!

faroutchris's avatar

@STAUDENMEIR - Thanks stuadenmeir! After renaming my model and fixing the other issues, this call correctly built the object I wanted :)

Please or to participate in this conversation.