如何在Eloquent中对数据透视表条目分组并显示数量（兼容only_full_group_by）

Hey there! Let's work through this problem where you're getting duplicate items from your belongsToMany association, and hitting that annoying sql_mode=only_full_group_by error when trying to group results.

Understanding the Issue #

Your inventory pivot table has multiple entries for the same item_id per user—hence the duplicate rows in your query result. When you tried grouping by inventory.item_id, MySQL's only_full_group_by mode blocked it because every column in your SELECT clause must either be in the GROUP BY or wrapped in an aggregate function (like COUNT, MAX, etc.). Since you're selecting columns from the items table (name, description) that aren't in your GROUP BY, MySQL throws that error.

Solution 1: Database-Level Grouping (Efficient for Large Datasets) #

We'll explicitly select all necessary columns from the items table, add a count of pivot entries, and group by all non-aggregated columns. This plays nicely with only_full_group_by:

$items = $user->inventory_items()
    ->selectRaw('items.*, COUNT(inventory.id) as item_count')
    // Group by every column from the items table that you're selecting
    ->groupBy('items.id', 'items.name', 'items.description')
    ->orderByDesc('inventory.id')
    ->paginate(25);

Then in your view, display the name with the count:

{{ $item->name }} (x{{ $item->item_count }})

Solution 2: Collection-Level Grouping (Flexible for Smaller Datasets) #

If you prefer not to mess with raw SQL, you can fetch all records first, then group them using Laravel's Collection methods. This handles the grouping in your application code instead of the database:

$items = $user->inventory_items()
    ->orderByDesc('inventory.id')
    ->get()
    // Group the collection by item_id
    ->groupBy('item_id')
    // For each group, keep the first item and add a count of group members
    ->map(function ($group) {
        $item = $group->first();
        $item->item_count = $group->count();
        return $item;
    })
    // Reset the collection keys to sequential numbers
    ->values()
    // Paginate the processed collection (Laravel 8+ supports this)
    ->paginate(25);

Why This Works #

• Solution 1 complies with only_full_group_by by including every non-aggregated column from items in the GROUP BY clause. The COUNT(inventory.id) gives you the number of times the item appears in the pivot table.
• Solution 2 avoids database grouping entirely by leveraging Laravel's powerful Collection methods. It's great for smaller datasets where performance isn't a critical concern, and it keeps your query cleaner.

内容的提问来源于stack exchange，提问作者GalahadXVI