phplaraveljoineloquentcounting

Join, group and count via Laravel Eloquent

I have two Models, cards and risks

Cards has the following columns:

1. id
2. weight
3. color  //There are four types of colors, green, red, yellow, blue

Risks has the following:

1. id
2. name
3. description
4. card_id

What I want to achieve is a count of how many risks are, grouped by colors, for example:

'color' => 'green'
'count' => '4'

'color' => 'blue'
'count' => '2'

'color' => 'red'
'count' => '6'

So far I haven't been able to make this query using Eloquent, maybe some DB::raw could help?

Solution 
  • Risk::select('card.name', DB::raw('count(risks.card_id)'))
->rightJoin('cards', 'card.id', '=', 'risks.card_id')
->groupBy('risks.card_id')
->get();