I have a pivot table with two foreign keys and I want to list items according to one foreign key.

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

I have relations between tables and I want to list tools by the user like this:

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

I loop the records but as you can guess it's listing as:

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

----or----


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

How can I list them as below?

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

This what tried so far:

$assign gets all data from pivot table and schema is in top of the post.

        <table >
            <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'));
    }

Pivot table's model

public function employee()

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

CodePudding user response：

I think you should change your relationship type from belongsTo to belongsToMany like this:

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class User extends Model
{
    // ...

    /**
     * Get the tools that the user belongs to.
     *
     * @return BelongsToMany
     */
    public function tools(): BelongsToMany
    {
        return $this->belongsToMany(Tool::class);
    }
}

This way you could do something like this: (not tested)

foreach (User::all() as $user) {
    echo $user->id . ' - ' . $user->name . ' - ' . $user->tool->pluck('id')->join(', ');
}

EDIT:

Here is an example for your code to achieve what you want, but it's really messy. I suggest you to try my code above to fix it.

    <table >
        <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>
                    {{ $assigns->where('employee_id', $assign->employee_id)->get()->map(function($tool) { return $tool->tool->tool_code; })->join(', '); }}
                </td>
            </tr>
        @endforeach
        </tbody>
    </table>

CodePudding user response：

I solved in hard way. It's not stupid if it works :)

<table>
    <thead>
        <tr>
            <th scope="col">Tool</th>
            <th scope="col">Employee</th>
            <th scope="col">Department</th>
        </tr>
    </thead>
    <tbody>
    @php($index = 1)
    @foreach($employees as $collect)
        @if(\App\Models\ToolAssign::where('employee_id', $collect->id)->count() > 0)
            <tr>
                <th scope="row">{{$index}}</th>
                <td>{{$collect->last_name." ".$collect->first_name}}</td>
                <td>{{$collect->department->name}}</td>
                <td>
                    @foreach(\App\Models\ToolAssign::where('employee_id', $collect->id)->get() as $codes)
                        @if($loop->last)
                            {{$codes->tool->tool_code}}
                        @else
                            {{$codes->tool->tool_code}},
                        @endif
                    @endforeach
                </td>
            </tr>
            @php($index   )
        @endif
    @endforeach
    </tbody>
</table>