Laravel eloquent count total of participants that belongs to departments of one specific event-CodePudding

My current query can count the number of participants that belongs to each department:

$departments = Department::select(['departments.id', 'departments.name'])
            ->with('participants')
            ->withCount('participants')
            ->orderByDesc('participants_count')
            ->groupBy('departments.name')
            ->groupBy('departments.id')
            ->get();

I have a table departments and another called participants. In the participants table there is fk_key called department_id of this way when one participant is beign registering I need the select his department.

Inside the model Department I have a relation hasMany with Participant:

 public function participants() {
        return $this->hasMany(Participant::class, 'department_id');
    }

Using this relation I can execute the query of above and I get something like this:

#DepartName   #participants_count
department_1  12
department_2  5
department_3  44
department_4  33

Here there is not problems for me. But the problems comes here. First, in my database exists a tabla called events and another table called event_participant. I can register events, and in a pivot table event_participant I can register the participants to the events and control the state of the payment of the participants of each event.

This pivot table has this columns:

event_participant

id  | event_id  | participant_id | payment_state

My model Event has a relation called participants and using this relation I can get all the participants that belongs of each event.

 public function participants() {
         return $this->belongsToMany(Participant::class, 'event_participant',
                                                      'event_id',
                                                      'participant_id')
                                                     ->withTimestamps();

}

Now I want count the total of participants of each department like the query of above but of one specific event.

For example: Exists two events, for the first event 10 participants were registered, of this 10 participants 5 belogns to the department A and 5 to the department B and all this participants belogns to the event 1. And in my database exists 5 departments I should get something like this for this example of the event one:

#departName     #participants_count
department_A    5
department_B    5
department_C    0
department_D    0
department_E    0

I hope you can understand me, this report is only for the event one. My idea is get all the departments with his total of participants of x event. To register the participants to the events I'm using a pivot table called event_participant.

Thanks so much.

Note. I'm using softdeletes on the Event, Department and Participant model.

CodePudding user response：

You can add additional constraints to the withCount.

I skipped other parts of your original query.

$eventName = 'xyz';

$departments = Department::withCount(['participants' => function ($query) use ($eventName) {
    $query->whereHas('events', function ($query) use ($eventName) {
        $query->where('name', $eventName);
    });
}])->orderByDesc('participants_count')->get();

https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models