如何获取包含用户总数及各状态用户数的所有Group数据？

Got it, let's work through this problem step by step. You want to pull all your Group records, and for each group, include the total number of users plus a breakdown of how many users fall into each status category (premium, gold, platinum). Here are two practical solutions depending on whether you're writing raw SQL or using an ORM like Laravel Eloquent:

This uses conditional aggregation with CASE WHEN to count users per status, paired with a LEFT JOIN to ensure groups with no users are still included (their counts will be 0).

SELECT 
    g.id,
    g.name,
    COUNT(u.id) AS total_users,
    COUNT(CASE WHEN u.status = 'premium' THEN 1 END) AS premium_users,
    COUNT(CASE WHEN u.status = 'gold' THEN 1 END) AS gold_users,
    COUNT(CASE WHEN u.status = 'platinum' THEN 1 END) AS platinum_users
FROM groups g
LEFT JOIN users u ON g.id = u.group_id
GROUP BY g.id, g.name
ORDER BY g.id;

How this works: #

• LEFT JOIN ensures we don't exclude groups that have no associated users.
• COUNT(u.id) gives the total number of users in the group (since u.id will be NULL for groups with no users, COUNT ignores those, returning 0).
• Each COUNT(CASE...) checks if the user's status matches the target value; if yes, it returns 1 (which gets counted), otherwise NULL (which COUNT ignores). This gives us the exact count for each status.

If you're using Laravel's ORM, you can use the withCount method to add these aggregated counts directly to your Group models.

Option 1: Using withCount with conditional clauses #

use Illuminate\Support\Facades\DB;
use App\Models\Group;

$groups = Group::query()
    ->withCount([
        // Total users per group
        'users as total_users' => function ($query) {
            $query->select(DB::raw('count(id)'));
        },
        // Count premium users
        'users as premium_users' => function ($query) {
            $query->where('status', 'premium');
        },
        // Count gold users
        'users as gold_users' => function ($query) {
            $query->where('status', 'gold');
        },
        // Count platinum users
        'users as platinum_users' => function ($query) {
            $query->where('status', 'platinum');
        }
    ])
    ->get();

Option 2: Using selectRaw with a join (mirrors the raw SQL approach) #

use Illuminate\Support\Facades\DB;
use App\Models\Group;

$groups = Group::query()
    ->leftJoin('users', 'groups.id', '=', 'users.group_id')
    ->select([
        'groups.id',
        'groups.name',
        DB::raw('count(users.id) as total_users'),
        DB::raw('count(case when users.status = "premium" then 1 end) as premium_users'),
        DB::raw('count(case when users.status = "gold" then 1 end) as gold_users'),
        DB::raw('count(case when users.status = "platinum" then 1 end) as platinum_users')
    ])
    ->groupBy('groups.id', 'groups.name')
    ->get();

Formatting the output to match your example #

Once you have the $groups collection, you can loop through it to format the results like your sample:

foreach ($groups as $group) {
    echo "[{$group->name} ({$group->total_users}) => [ premium ({$group->premium_users}), gold ({$group->gold_users}), platinum ({$group->platinum_users})]]\n";
}

内容的提问来源于stack exchange，提问作者Dr. Alexander