Retrieving data from pivot table in Laravel only using one foreach loop

I have created the 3-way pivot table for Users, Shifts, Patterns. It is called pattern_shift_user.

[pattern_shift_user.][1]

Schema::create('pattern_shift_user', function (Blueprint $table) {
            //$table->primary(['pattern_id', 'shift_id', 'user_id']);
            $table->unsignedBigInteger('pattern_id');
            $table->unsignedBigInteger('shift_id');
            $table->unsignedBigInteger('user_id');
            $table->timestamps();

            $table->foreign('shift_id')->references('id')->on('shifts')->onDelete('cascade')->onUpdate('cascade');

            $table->foreign('pattern_id')->references('id')->on('patterns')->onDelete('cascade')->onUpdate('cascade');

            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
        });

It refers to the three models via many to many relationships here’s a view of the user model that links the pattern model and shift model [User Model][2]

public function patterns()
    {
        return $this->belongsToMany('AppPattern', 'pattern_shift_user')->withPivot('shift_id');
    }

    public function shifts()
    {
        return $this->belongsToMany('AppShift', 'pattern_shift_user')->withPivot('pattern_id');
    }

I have done the same for each model, and it seems to work,

the problem I now have is getting the info into a table on Laravel. I want 3 columns headed DATE/DAY, SHIFT, STATION.

I can get all the info I want to display out. However, I can’t get the info to separate into columns because I’m using more than one for each loop to refer to different tables, This is the Controller for my view; I’m using index [Controller index][3]

public function index()

    {
$user = Auth::user();
        $testUser = User::all();
        $shift = Shift::all();
        $pattern = Pattern::all();


        return view('layouts/timesheet/index', ['user' => $user, 'shift' => $shift, 'pattern' => $pattern, 'testUser' => $testUser]);
    }

then in the blade file (I have played around loads with this), it currently looks like this

[Index.blade folder][4]

<table class="table table-sm table-success">

                <tr>
                    <th scope="col">DATE/DAY</th>
                    <th scope="col">SHIFT</th>
                    <th scope="col">STATION</th>
                </tr>

                <tbody class="table-light">
                    @foreach (Auth::user()->patterns as $patternUser)
                        @foreach ($user->shifts as $shiftUser)

                            <tr>
                                <td>{{ $patternUser['day_of_week'] }}</td>
                                <td>{{ $shiftUser['Name'] }}</td>
                            </tr>
                        @endforeach
                    @endforeach

                </tbody>
            </table>

I can either get my dates and shifts to display all under the DATE/DAY column, or I can get 7 Mondays, 7 Tuesdays etc. to display in the DATE/DAY column and the SHIFT column; the 7 shits will keep looping so I have 7×7 list of shifts,

I’m massively out of my depth with this, and I’m not very good at explaining, so if there is more info I can provide, please let me know.

Thank you for any help

( I haven’t added the stations yet as I’m finding just two columns difficult enough. Eventually, I will need to create a bigger table with many more columns, so I just wanted to understand this on a small scale first)

Edited to include updated models,

User Model a User will have one pattern each, but will be given many shifts (as sometimes they will pick up additional shifts outside the pattern)

[User model][1]

public function patterns()
    {
        return $this->belongsToMany('AppPattern', 'pattern_shift_user')->withPivot('pattern_id');
    }

    public function shifts()
    {
        return $this->belongsToMany('AppShift', 'pattern_shift_user')->withPivot('shift_id');
    }

Pattern Model, this is simply a table to show dates, days of weeks, highlight if bank holiday, it will have many shifts attached, and will have many users on the same pattern.

    class Pattern extends Model
{
    public function shifts()
    {
        return $this->belongsToMany('AppShift', 'pattern_shift_user')->withPivot('shift_id');
    }

    public function users()
    {
        return $this->belongsToMany('AppUser', 'pattern_shift_user')->withPivot('user_id');
    }
}

Shift model (this is just times of each shift there will be about a 100 of them) belongs to many patterns, and many users could have the same shifts

class Shift extends Model
{
    public function patterns()
    {
        return $this->belongsToMany('AppPattern', 'pattern_shift_user')->withPivot('pattern_id');
    }

    public function user()
    {
        return $this->belongsToMany('AppUser', 'pattern_shift_user')->withPivot('user_id');
    }
}

Answer

I have an idea, first, you need to stick to one type of relation.

Assuming that the pattern is the top part of your hierarchy, so you can return the $patterns only from your controller.

public function index()
    {
        $patterns = Pattern::all();
        return view('layouts/timesheet/index', ['pattern' => $pattern]);
    }

Then, inside the view, this is more like pseudo-code traversing on relationships hierarchically, like:

    foreach ( $patterns as $pattern)
    
       // print all the pattern data like pattern[day_of_week] etc..
    
          foreach($pattern->shifts as $shift)
    
             // print all the shift data like shift[name] etc..
    
                foreach ($shift->users as $user)
    
                   // print all the shift data like user[name] etc.. 

                @endforeach
          @endforeach
   @endforeach

This is the logic I can think of, you can replace the patterns with what do you think is the top of your data hierarchy, and tweak the rest of the pseudo to print the data nicely.

Advertisement

Answer