Laravel Eloquent Get the Group with the highest Member Points

General
1

I have two tables, Members and WhatsAppGroup.

Each Member belongs to a WhatsAppGroup and a WhatsAppGroup hasMany Members.

Each Member accumulates Points for their activities. On the Members table is the Points column and the WhatsAppGroup_ID column.

So with that column, it is easy to return the list of top Members like this

$MemberPoints = Member::where('point','!=' , 0)->orderBy('point','desc')->take(10)->get();

Now I want to return the top WhatAppGroup with the highest Members Points

However, I’m unable to come up with a query to get the top WhatAppGroup

2

To get the top WhatsAppGroup with the highest total Points accumulated by its Members, you can use a query that aggregates the total points per group and orders by the sum in descending order.

In Laravel’s Eloquent or Query Builder, this can be done using a join, groupBy, and orderBy structure.

Here’s how you can do it:

Option 1: Using Laravel Query Builder

$topGroups = DB::table('members')
    ->select('whatsapp_group_id', DB::raw('SUM(point) as total_points'))
    ->where('point', '!=', 0)
    ->groupBy('whatsapp_group_id')
    ->orderByDesc('total_points')
    ->take(10)
    ->get();

If you want to include the group name or other fields from the whatsapp_groups table:

$topGroups = DB::table('members')
    ->join('whatsapp_groups', 'members.whatsapp_group_id', '=', 'whatsapp_groups.id')
    ->select('whatsapp_groups.id', 'whatsapp_groups.name', DB::raw('SUM(members.point) as total_points'))
    ->where('members.point', '!=', 0)
    ->groupBy('whatsapp_groups.id', 'whatsapp_groups.name')
    ->orderByDesc('total_points')
    ->take(10)
    ->get();

Option 2: Using Eloquent Relationships (if defined)

Assuming your models are set up like this:

  • WhatsAppGroup has hasMany('App\Member')
  • Member belongs to WhatsAppGroup

You could do:

$topGroups = WhatsAppGroup::withSum(['members as total_points' => function($query) {
    $query->where('point', '!=', 0);
}], 'point')
->orderByDesc('total_points')
->take(10)
->get();