Retrieve all data with specific information from pivot table using Eloquent

Question

I have a many-to-many relationship between users and courses, with a pivot table Users_Courses containing the isComplete value, but I can't seem to do it without looping through each user and getting their Retrieve the isComplete value in case of a course and then loop through each course to get the pivot data.

All the examples I've found map the isComplete value to a class with a loop, but this seems to be extremely taxing on the program and I don't really find it attractive, That's why I'm making my own question here. If I haven't seen an answer to this question, please link it below since I can't seem to find it.

Also, I'm using Laravel-9 and MySQL.

The data structure I retrieve now is as follows:

"data": [
        {
            "id": 2,
            "fname": "name",
            "lname": "last name",
            "email": "mail@mail.com",
            "courses": [
                {
                    "id": 1,
                    "name": "test_course_1",
                    "description": "this is a test course for testing"
                },
                {
                    "id": 2,
                    "name": "test_course_2",
                    "description": "this is also a test course"
                },
                {
                    "id": 3,
                    "name": "test_course_3",
                    "description": "this course is a test course"
                }
            ]
        }
    ]

I'm looking for a way to retrieve the pivot value isComplete with Eloquent and get the data for the class itself, like this or something similar.

In other words, I want to check if the user completed the course via the pivot table value isComplete as shown in the example below. < /p>

"data": [
        {
            "id": 2,
            "fname": "name",
            "lname": "last name",
            "email": "mail@mail.com",
            "courses": [
                {
                    "id": 1,
                    "name": "test_course_1",
                    "description": "this is a test course for testing",
                    "isComplete": 1
                },
                {
                    "id": 2,
                    "name": "test_course_2",
                    "description": "this is also a test course",
                    "isComplete": 0
                },
                {
                    "id": 3,
                    "name": "test_course_3",
                    "description": "this course is a test course",
                    "isComplete": 0
                }
            ]
        }
    ]

My current code looks like this:

class User extends Authenticatable
{
    public function courses()
    {
        return $this->belongsToMany(Course::class, 'user_courses')
            ->withPivot('isCompleted');
    }
}

class Course extends Model
{
    public function users()
    {
        return $this->belongsToMany(User::class, 'user_courses')
            ->withPivot('isCompleted');
    }
}

class UserController extends Controller
{
    public function getUsersById(int $user_id)
    {
        try {
            $users = User::where('id', $user_id)
                ->with('courses')
                ->get();

            return response()->json([
                'success' => true,
                'data' => $users
            ]);
        } catch (Throwable $th) {
            return response()->json([
                'success' => false,
                'data' => null,
                'message' => $th,
            ]);
        }
    }
}

I know it's called isCompleted in the code, but it's also called that in the database. This is a typo that hasn't been fixed yet: D

Answer 1

In other words, I want to check if the user has completed the course via the pivot table value isComplete as shown in the example below.

Did you read about filtering with pivot table columns in the documentation: https://laravel.com/docs/9.x/eloquent-relationships#filtering-queries-via-intermediate- table-columns

If you only need completed courses, you can call the relationship

$users = User::where('id', $user_id)
        ->with(['courses' => function($query) {
            $query->wherePivot('isCompleted', 1);  // use quotes if its datatype is enum in database.
        }])
        ->get();

Or you can establish custom relationships for completed and unfinished in the model.

class User extends Authenticatable
{
    public function courses()
    {
        return $this->belongsToMany(Course::class, 'user_courses')
            ->withPivot('isCompleted');
    }

    public function completedCourses()
    {
        $this->courses()->wherePivot('isCompleted', 1);
    }
    
    public function InCompleteCourses()
    {
        $this->courses()->wherePivot('isCompleted', 0);
    }
}

In the user controller you can call them

$users = User::where('id', $user_id)
            ->with('completedCourses')
            ->get();