DB Query Help

hey guys looking for some help create a laravel query...

basically I have a Course and User Models, I also have a Complete Model.

courses have many Users

Users have many Compete

Complete belong to Users and Courses

How get I create a query that returns a collection of all Courses with associated users and and wether or not they have completed it?

looking into sub querys on laracasts and stuff just exactly sure which route i need to be looking into?? many thanks

tykus

4 years ago

Level 104

You appear to have a many-to-many relationship between User and Course models; the Complete model may be superfluous. I assume you have a column on the intermediate table which determines whether the User has completed the Course?

Let's assume the intermediate table is named completes and it has a boolean column is_completed:

// Course model
public function users()
{
    return $this->belongsToMany(User::class, 'completes')->withPivot('is_completed');
}

// Get all courses with the enrolled users
$courses = Course::with('users')->get();

In the view, you need a nested loop to iterate over the $courses and its users. You can get the is_completed value using the pivot property from the relationship:

<ul>
@foreach($courses as $course)
    <li>{{ $course->name }}</li>
    <ul>
    @foreach($course->users as $user)
        {{ $user->name }} - {{ $user->pivot->is_completed ? '✅' : '❌' }}
    @endforeach
    </ul>
@endforeach
</ul>

1 like

willnjl

4 years ago

Level 2

@tykus

Thanks. The Complete model is polymorphic which is throwing a spanner in the work

Schema::create('completes', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->unsigned();
            $table->morphs('completable');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->timestamps();
            $table->unique(['user_id', 'completable_type', 'completable_id']);
        });

tykus

4 years ago

Level 104

@willnjl so it is a polymorphic many-to-many; with the correct relationships, you can achieve the same outcome

https://laravel.com/docs/9.x/eloquent-relationships#many-to-many-polymorphic-relations

// Course
public function users()
{
    return $this->morphToMany(User::class, 'completable');
}

$courses = Course::with('users')->get();

Please or to participate in this conversation.