I have a many to many relationship between Users & Courses with a pivot table, Users_Courses, containing an isComplete value, but i can't seem to retrieve the isComplete value without looping through every user, getting their courses and then looping over every course getting the pivot data.

All the examples i have found is to map the isComplete value to the course with loops, but that seems like it's awfully taxing on the program and i don't really find it appealing which is why I'm making my own question here. If there's already an answer to this that i haven't seen please link it below as i can't seem to find it.

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

The data structure I'm retrieving right now looks like this:

"data": [
        {
            "id": 2,
            "fname": "name",
            "lname": "last name",
            "email": "[email protected]",
            "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 searching for a way to retrieve the pivot value isComplete with Eloquent and getting the data with the course itself like this or something like it.

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

"data": [
        {
            "id": 2,
            "fname": "name",
            "lname": "last name",
            "email": "[email protected]",
            "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
                }
            ]
        }
    ]

The code i have right now 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 am aware that it's called isCompleted in the code, but it's also called that in the database. It's a typing error which haven't yet been fixed :D

CodePudding user response：

if you want the output to be like the JSON:

$user = User::with("courses")->find(1);
$user = $user->courses->each(
  function($course) {
    $course->isComplete = $course->pivot->isComplete;
    unset($course->pivot);
  }
);

this line will retrieve Courses with an object pivot, which includes the columns of your pivot table.

(Example)

CodePudding user response：

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

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

If you need only completed courses you can call relation as

$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 make customized relations for completed, Incompleted in your 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);
    }
}

And in user controller you can call them as

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