Laravel Eloquent 查询成员积分最高的WhatsApp群组

Hey there! Looks like you need to find the WhatsApp group with the highest total points from its members. Let's break this down step by step using Laravel Eloquent.

First, double-check that your relationship in the WhatsAppGroup model is correctly defined (since you mentioned a one-to-many relationship already, this is just a quick sanity check):

// app/Models/WhatsAppGroup.php
public function members()
{
    return $this->hasMany(Member::class, 'WhatsAppGroup_ID');
}

The Cleanest Eloquent Approach (Using withSum) #

Laravel's Eloquent has a handy withSum method that makes aggregating related model values a breeze. We can use this to calculate the total points for each group, then sort and pick the top one:

// Get the top group with highest total member points
$topGroup = WhatsAppGroup::withSum('members', 'point')
    // Optional: Exclude groups where all members have 0 points
    ->whereHas('members', function ($query) {
        $query->where('point', '!=', 0);
    })
    ->orderByDesc('members_sum_point')
    ->first();

Let me explain what each part does:

• withSum('members', 'point'): This calculates the sum of the point column from all related members for each group, and stores the result in a property called members_sum_point.
• whereHas(...): This filters out groups that have no members with non-zero points (you can remove this line if you want to include groups with total 0 points too).
• orderByDesc('members_sum_point'): Sorts the groups from highest total points to lowest.
• first(): Grabs the first (top) group from the sorted list.

Once you have $topGroup, you can access the total points using $topGroup->members_sum_point.

Alternative: Using a Join and Raw SQL #

If you prefer using a join for more control, here's another way to do it:

use Illuminate\Support\Facades\DB;

$topGroup = WhatsAppGroup::select('whats_app_groups.*', DB::raw('SUM(members.point) as total_points'))
    ->join('members', 'whats_app_groups.id', '=', 'members.WhatsAppGroup_ID')
    ->where('members.point', '!=', 0)
    ->groupBy('whats_app_groups.id')
    ->orderByDesc('total_points')
    ->first();

Note: If your MySQL is running in strict mode, you might need to adjust your config/database.php settings to disable strict mode, or include all columns from whats_app_groups in the groupBy clause (though the withSum method avoids this hassle entirely).

Handling Ties #

If multiple groups have the exact same highest total points, first() will only return one of them. If you need all groups tied for first place, you can first get the maximum total points, then fetch all groups that match that total:

$maxTotal = WhatsAppGroup::withSum('members', 'point')
    ->whereHas('members', fn($q) => $q->where('point', '!=', 0))
    ->max('members_sum_point');

$topGroups = WhatsAppGroup::withSum('members', 'point')
    ->where('members_sum_point', $maxTotal)
    ->get();

That should cover all your cases! Let me know if you run into any issues.

内容来源于stack exchange