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

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0