Laravel Blade中按用户分组展示数据透视表关联工具

我有一个包含两个外键的数据透视表（pivot table），结构如下：

id | tool_id | user_id
______________________
1    2         12
2    5         12
3    3         12
4    4          7

已建立表间关联，希望按用户分组展示工具，预期效果：

No: User  Tools
--- ----  -----
  1 John  2,3,5
  2 Sara  4

但当前循环代码会出现重复行或重复工具列表的问题，以下是我尝试的代码：

Blade视图代码 #

<table class="table mb-0">
    <thead>
    <tr>
        <th scope="col">Tool</th>
        <th scope="col">Employee</th>
        <th scope="col">Department</th>
    </tr>
    </thead>
    <tbody>
    @foreach($assigns as $assign)
        <tr>
            <td>{{$assign->employee->last_name." ".$assign->employee->first_name}}</td>
            <td>{{$assign->employee->department->name}}</td>
            <td>
                @foreach($assigns->where('employee_id', $assign->employee_id) as $tool)
                    {{$tool->tool->tool_code}}
                @endforeach
            </td>
        </tr>
    @endforeach
    </tbody>
</table>

Controller代码 #

public function assignLetter(){
    $assigns = ToolAssign::all();
    $employees = Employee::where('status', 1)->where('is_inspector', 1)->orderBy('last_name')->get();
    return view('tool.assign', compact('assigns', 'employees'));
}

数据透视表模型代码 #

public function employee()
{
    return $this->belongsTo(Employee::class, "employee_id", "id");
}

public function tool()
{
    return $this->belongsTo(Tool::class, "tool_id", "id");
}

请问如何修改代码，实现按用户分组、合并展示工具的预期效果？

核心思路是提前按用户完成数据分组，避免视图层重复遍历生成冗余行，同时优化查询避免N+1性能问题。

方案一：从Employee模型出发（推荐） #

1. 补充Employee模型关联 #

在Employee模型中添加与ToolAssign的关联：

public function toolAssigns()
{
    return $this->hasMany(ToolAssign::class, 'employee_id', 'id');
}

2. 修改Controller代码 #

预加载关联数据，只查询有工具分配的目标员工：

public function assignLetter(){
    $employees = Employee::where('status', 1)
        ->where('is_inspector', 1)
        ->orderBy('last_name')
        ->with(['toolAssigns.tool', 'department']) // 预加载关联，避免N+1查询
        ->has('toolAssigns') // 仅保留有工具分配的员工
        ->get();

    return view('tool.assign', compact('employees'));
}

3. 修改Blade视图代码 #

直接遍历分组后的员工数据，用implode快速合并工具编码：

<table class="table mb-0">
    <thead>
    <tr>
        <th scope="col">No</th>
        <th scope="col">Employee</th>
        <th scope="col">Department</th>
        <th scope="col">Tools</th>
    </tr>
    </thead>
    <tbody>
    @foreach($employees as $index => $employee)
        <tr>
            <td>{{ $index + 1 }}</td>
            <td>{{ $employee->last_name . ' ' . $employee->first_name }}</td>
            <td>{{ $employee->department->name }}</td>
            <td>
                {{ $employee->toolAssigns->pluck('tool.tool_code')->implode(', ') }}
            </td>
        </tr>
    @endforeach
    </tbody>
</table>

方案二：直接对ToolAssign分组（无需修改Employee模型） #

1. 修改Controller代码 #

对ToolAssign按员工ID分组，同时预加载关联数据：

public function assignLetter(){
    $assignGroups = ToolAssign::with(['employee.department', 'tool'])
        ->get()
        ->groupBy('employee_id');

    return view('tool.assign', compact('assignGroups'));
}

2. 修改Blade视图代码 #

遍历分组后的数据集，提取员工信息并合并工具编码：

<table class="table mb-0">
    <thead>
    <tr>
        <th scope="col">No</th>
        <th scope="col">Employee</th>
        <th scope="col">Department</th>
        <th scope="col">Tools</th>
    </tr>
    </thead>
    <tbody>
    @foreach($assignGroups as $index => $assigns)
        @php $employee = $assigns->first()->employee; @endphp
        <tr>
            <td>{{ $index + 1 }}</td>
            <td>{{ $employee->last_name . ' ' . $employee->first_name }}</td>
            <td>{{ $employee->department->name }}</td>
            <td>
                {{ $assigns->pluck('tool.tool_code')->implode(', ') }}
            </td>
        </tr>
    @endforeach
    </tbody>
</table>

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