Laravel fetch intermediate table data with many to many relationship-CodePudding

I have 3 tables user, specialities & results

results contain specialitie_id, user_id

I have create many-to-many relationship

Here's the relationship in user model

public function specialities()
    {
        return $this->belongsToMany(Speciality::class, 'results', 'user_id', 'specialitie_id')->withPivot('result', 'color');
    }

I want to fetch data from the result table based on specialitie_id

If my url is abc.com/result/5

I have attached table structure as well,

it should show me result related to specialitie_id 5 I tried this but it doesn't work

$result = User::with('specialities')->where('id', 5)->get();

Any help would be appreciated.

CodePudding user response：

Your current query:

$result = User::with('specialities')->where('id', 5)->get();

Is saying, give me all results where the USER_ID = 5 WITH all the specialities related to it.

You are very close but did it backwards:

//Give me all results where the SPECIALITY_ID = 5 
//WITH all the users related to it
$result = Speciality::with('users')->where('id', 5)->get();

Please note that the above is an array

Example of a Controller Function:

public function results($id){
    $results = Speciality::with('users')->where('id', $id)->get(); 

    return view('user_specialities', compact('results'));
}

Example view user_specialities:

@foreach($results as $result)
    <p>Result: {{$result->result}}</p>
    <p>Formulary: {{$result->formulary}}</p>
    <p>Color: {{$result->color}}</p>
    <p>User ID: {{$result->user->id}}</p>
    <p>User Name: {{$result->user->name}}</p>
@endforeach

Speciality Model:

public function users()
{
    return $this->belongsToMany(User::class, 'results', 'specialitie_id', 'user_id')->withPivot('result', 'color');
}

User Model:

public function specialities()
{
    return $this->belongsToMany(Speciality::class, 'results', 'user_id', 'specialitie_id')->withPivot('result', 'color');
}