laravel pivot table with condition

Question

Currently I have a problem with accessing data from pivot table.

Here my setup, I have 3 models:

• User
• Channels
• Access (as pivot, contain user_id & channel_id)

I setup relationship using:

public function channels()
    {
        return $this->belongsToMany('App\Models\Channel', 'cms_access', 'user_id', 'channel_id')
                ->withPivot('access_analytic', 'access_live_channel', 'access_linear_channel', 'access_ads', 'access_push_notification')
                ->withTimestamps();
    }

I tried to build a page that show detail of user with his/her access to table:

public function show($id)
{
    $user = User::findOrFail($id);

    // whereHas seems not working, cannot filter to specific id only
    $channels = Channel::with('users')
                        ->whereHas('users', function ($query) use($user) {
                            $query->where('users.id', '=', $user->id);
                        }) 
                        ->where('company_id', '=', $user->company_id)
                        ->where('active', '=', 1)
                        ->get();

    return view('user.show')
            ->with('user', $user)
            ->with('channels', $channels);
}

Currently using table:

<tbody>
    @forelse ($channels as $channel)
        {!! Form::open(['route' => ['access.store'], 'method' => 'POST']) !!}
        <tr>
            <td>
                <img src="{{ minio_url('channels/'.$channel->logo) }}" alt="{{ $channel->logo }}" class="img-responsive">
            </td>
            <td>{{ $channel->name_eng }}</td>
            <td>
                {!! Form::select('access_analytic', ['none' => 'None', 'read' => 'Read'], @$channel->users[0]->pivot->access_analytic, ['class' =>'form-control']) !!}
            </td>
            <td>
                {!! Form::select('access_live_channel', ['none' => 'None', 'read' => 'Read', 'read/write' => 'Read/Write', 'read/write/delete' => 'Read/Write/Delete'], @$channel->users[0]->pivot->access_live_channel, ['class' =>'form-control']) !!}
            </td>
            <td>
                {!! Form::select('access_linear_channel', ['none' => 'None', 'read' => 'Read', 'read/write' => 'Read/Write', 'read/write/delete' => 'Read/Write/Delete'], @$channel->users[0]->pivot->access_linear_channel, ['class' =>'form-control']) !!}
            </td>
            <td>
                {!! Form::select('access_push_notification', ['none' => 'None', 'read' => 'Read', 'read/write' => 'Read/Write', 'read/write/delete' => 'Read/Write/Delete'], @$channel->users[0]->pivot->access_push_notification, ['class' =>'form-control']) !!}
            </td>
            <td class="text-center">
                {!! Form::hidden('user_id', $user->id) !!}
                {!! Form::hidden('channel_id', $channel->id) !!}
                {!! Form::submit('Save', ['class' =>'btn btn-primary']) !!}
            </td>
        </tr>
        {!! Form::close() !!}
    @empty
        <tr>
            <td colspan="6" class="text-center">No channel available!</td>
        </tr>
    @endforelse
    </tbody>

I don't want to use $channel->users[0]->pivot->access_analytic, etc as it doesn't work all the times due to whereHas not working, sometimes the actual user is located in users[1], any other suggestion on how I supposed to access the relationship?

Answer 1

If you only want to get one user with each channel and get only get channels for the same user, you need to combine with() and whereHas() like this:

Channel::with(['users' => function($q) use($user) {
        $q->where('id', $user->id);
    }])
    ->whereHas('users', function ($q) use($user) {
        $q->where('id', $user->id);
    }) 
    ->where('company_id', $user->company_id)
    ->where('active', 1)
    ->get();

In this case, only one user will be loaded. So you'll be able to get pivot data with:

$channel->users->first()->pivot->access_analytic

Or:

$channel->users[0]->pivot->access_analytic

If you don't want to filter channels by a user, just remove the whereHas() part.