JOIN 3 tables & Find Value in Eloquent Relationship

@michaloravec Normally, we assigned specific courses for some users.

Eg : Spoken English course assigneed to David, Grade_05 is Main Category, English is sub category,Spoken English is course name

yes, i want to get users which have assigned course.

So use has or whereHas if you need to be more specific.

Docs: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

@michaloravec , i used it, but i got this message

{message: "Call to undefined relationship [subCategory] on model [App\Models\User].",…}
exception: "Illuminate\Database\Eloquent\RelationNotFoundException"
file: "C:\wamp64\www\lms\vendor\laravel\framework\src\Illuminate\Database\Eloquent\RelationNotFoundException.php"
line: 34
message: "Call to undefined relationship [subCategory] on model [App\Models\User]."
trace: [{file: "C:\wamp64\www\lms\vendor\laravel\framework\src\Illuminate\Database\Eloquent\Builder.php",…},…]

subCategory and mainCategory adre relationships on Course model so

$users = User::with(['courses' => function ($query) {
    $query->with(['subCategory', 'mainCategory']);
}])->has('courses')->get();

@michaloravec , so far so good.

I want to get specific user's records

so i used this query. it gives me null array

 $users = User::with(['courses' => function ($query) {
                $query->with(['subCategory', 'mainCategory']);
            }])->has('courses','=',$userId)->get();

To retrieve a specific user.

$user = User::with(['courses' => function ($query) {
    $query->with(['subCategory', 'mainCategory']);
}])->findOrFail($userId);

okay . i used following one. is that also correct ?

  $users = User::with(['courses' => function ($query) {
                $query->with(['subCategory', 'mainCategory']);
            }])->where('users.id','=',$userId)->get()->toArray();

With ->where('users.id','=',$userId)->get() you get a collection but only with one user, because id is unique.

In that case is better to have just

$user = User::with(['courses' => function ($query) {
    $query->with(['subCategory', 'mainCategory']);
}])->findOrFail($userId);

By the way instead of

->where('users.id', '=', $userId)

It could be just

->where('id', $userId)

Its my bad. thanks @michaloravec , Appreciate your kind help !!

Each course has their own main category. So it can't be just one.

$mainCategories  = $data->courses->pluck('mainCategory'); // it will be a collection of main categories

Sorry i didn't get that . i want to get "All Main Categories" in this nature.

Currently i used this option . are there any professional way to do this ?

            $data = [];
            foreach ($userAssignedCourses as $uac) {
                $userAssignedMainCat= $uac->mainCategory;
                array_push($data,$userAssignedMainCat);
            }

I think this will work

$mainCategories = $data->courses->pluck('mainCategory');

code

//Get Assigned Users Courses
                $userAssignedCourses = User::with(['courses' => function ($query) {
                    $query->with(['subCategory', 'mainCategory']);
                }])->findOrFail($userId);

                $userAssignedCourses = $userAssignedCourses->courses;

                $data = $userAssignedCourses->courses->pluck('mainCategory');

error

message: "Property [courses] does not exist on this collection instance.", exception: "Exception",…}
exception: "Exception"
file: "C:\wamp64\www\lms\vendor\laravel\framework\src\Illuminate\Collections\Traits\EnumeratesValues.php"
line: 865
message: "Property [courses] does not exist on this collection instance."
trace: [{file: "C:\wamp64\www\lms\app\Http\Controllers\MainCategoryController.php", line: 48,…},…]

In this case

$userAssignedCourses = User::with(['courses' => function ($query) {
    $query->with(['subCategory', 'mainCategory']);
}])->findOrFail($userId);

$userAssignedCourses = $userAssignedCourses->courses;

$data = $userAssignedCourses->pluck('mainCategory');

@michaloravec fixed. i used this code. Thanks !!!

 $data = $userAssignedCourses->courses->pluck('mainCategory');