belongsToMany and count

Hi all,

I have 3 tables : - agencies - categories - agency_category (pivot table)

In the models Agency.php and Category.php I have set a belongsToMany relationship. Now I want to know the percentage of agencies in each category. How can I do ?

bart

11 years ago

Level 13

What about using the count() method?

$categories = Categories::with('agencies')->get();
$amount = Agency::all()->count();

foreach($categories as $category) {
    echo 'Percentage: ' . $category->agencies()->count() / $amount * 100 . "%\n";
}

1 like

bestmomo

11 years ago

Level 52

Or a solution with one query :

$result = Agency::join('agency_category', 'agency_category.agencies_id', '=', 'agencies.id')
    ->groupBy('agencies.id')
    ->get(['agencies.*', DB::raw('COUNT(agency_category.id) * 100 / (SELECT COUNT(*) FROM categories) as count')]);

foreach ($result as $r) {
    echo $r->id . '   ' . $r->count . '%<br>';
}

Hum I think I made the inverse of your purpose but the principe is good ;)

nolros

11 years ago

Level 23

math is :

percent of agencies in a category = ( #agencies in a category / all agencies) * 100

Please or to participate in this conversation.