Retrieve data from many-to many relationship

I have a problem to retrieve data from two tables (courses and profiles) between which a many-to-many relationship exists, probably because one of them (profiles) is a relational table.

Model: Course

public function profiles()
{
    return $this->hasMany('App\Profile');
}

Model: Profile

protected $ primaryKey  = "user_id";
public $incrementing =  false;

public function courses()
{
    return $this->belongsToMany('App\Course');
}

Pivot table: course_profile

$table->integer('profile_id')->unsigned()->index();
$table->foreign('profile_id')->references('user_id')->on('profiles')->onDelete('cascade');

$table->integer('course_id')->unsigned()->index();
$table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');

$table->primary(['profile_id', 'course_id']);

Controller: PageController

public function courses()
{
    $courses = Course::all()->load('profiles');

    return view('pages.courses', compact('courses'));
}

View: courses.php

@foreach($courses as $course)
    @foreach($course->profiles as $profile)
        {{ $ profile -> last_name}}
    @endforeach
@endforeach

Error message: Column not found: Unknown column 'profiles.course_id' in 'where clause' (SQL: select * from `profiles` where `profiles`.`course_id` in (1, 2, 3, 4, 5))

SaeedPrez

9 years ago

Best Answer

Level 50

In many to many, both relationships need to be defined as return $this->belongsToMany(Model::class);

https://laravel.com/docs/5.3/eloquent-relationships#many-to-many

willaguilar

9 years ago

Level 6

Try the following:

public function courses()
{
    // Eager load is your friend
    $courses = Course::with('profiles')->get();
    return view('pages.courses', compact('courses'));
}

2 likes

minjon

9 years ago

Level 15

@SaeedPrez Thanks a lot, both sides of the relationship work now. I instinctively followed my own logic instead of Laravel's. LOL

2 likes

Please or to participate in this conversation.