How to filter and count in relation in laravel

I am trying to complicate a request to use only that in my controller. My request counts the events that took place in the past year and returns me the total grouped by month for to show it with chartjs. Currently my request is

$monthly_uploaded_product = Evenement::with('animal')
->select(DB::raw('COUNT(id) as total, EXTRACT(MONTH FROM datedevenement) AS month')
    )
        ->where('typeevenement_id', '1')
        ->wheredate('datedevenement', '>=', Carbon::now()->lastOfMonth()->subyears(1))
        /// ->whereYear('datedevenement', Carbon::now()->year)
        ->groupBy(DB::raw('month'))
        ->get();
    dd($monthly_uploaded_product);
    $mois  = ["janvier", "fevrier", "mars", "avril", "mai", "juin", "juillet", "aout", "septembre", "octobre", "novembre", "decembre"];
    foreach ($mois as $mois99) {
        $arriveeparmoisrefuge[]= array( /// create final array and set 0 to all total
            'mois' => $mois99,
            'total' => '0'
        );
    }
    foreach ($monthly_uploaded_product as $key) {
        $arriveeparmoisrefuge[$key->month - 1]['total'] = $key->total;//update each month with the total value if there is value in the month
    }
    $sorted = collect($arriveeparmoisrefuge)->sortBy(function ($count, $month) { /// reorder for to show in first the current month
        $currentMonth = (int) \Carbon\Carbon::now()->month;
        return ($month + (13 - $currentMonth - 1)) % 12;
    });
    return  $sorted;
}

My query queries the "evenement" table and returns a array like this:

month => 1 Total => X, month => 2 Total => X etc...

I have the month name and the total of event for this month so all is ok.

Now evenement is attached to animal which can be male or femelle :

public function evenements() {
    return $this->hasMany(Evenement::class, 'animal_id');
}

public function animal()
{
    return $this->belongsTo(Animal::class, 'animal_id')->withTrashed();
}

Now that I have the total of the events for each month, I am looking for the distribution by male or female animals so a array like this:

month => 1 total_male => X Total_femelle => Y Total => X+Y 
month => 2 total_male => X Total_femelle => Y Total => X+Y 

I try groupby and sortby but it does not work. I don't found solutions for count via relationship between evenement and animal table

from Newest questions tagged laravel-5 - Stack Overflow http://bit.ly/2J6LMM0
via IFTTT