I have this code I want to get list users with a role of teacher where each teacher will have one object for the personal detail one object for the school detail

 public function index(){
  $teachers = User::whereHas('roles' , function($q){$q->where('name','isTeacher');})->get();
            foreach($teachers as $teacher){
                $teacher_id = $teacher->id;
                $teacherinSchool = School_Teachers::where('user_id', $teacher_id)->first();
               $teacherinSchool = $teacherinSchool->school_id;
                $School = School::where('id', $teacherinSchool)->first();
                return response(['teacher'=>$teacher, 'school'=>$School]); 
            }
    }

this is what i got but i am expecting to have more that one teacher but it takes the first teacher in the list and display the objects

output in the postman

i have 5 models involved here User model, Role model, User_Role model, school model and school_teacher model

CodePudding user response：

Few things to point out

• You are doing queries within a loop (foreach) not good for performance.
• Having return response() within foreach loop hence only 1 Teacher record is available
• You are getting just the first record for School_Teachers & School

For what you are trying to do can be done more efficiently as under

public function index()
{
    $teachers = User::whereHas('roles', fn($query) => $query->where('name', 'isTeacher'))->get();

    $schoolIds = School_Teachers::whereIn('user_id', $teachers->pluck('id')->toArray())->pluck('id')->toArray();

    $schools = School::whereIn('id', $schoolIds)->get();

    return response()->json(['teachers' => $teachers, 'schools' => $schools]);
}

However that is also not optimal, still can be better

• Define a relation on User model to link it to School model via School_Teachers (many-to-many)
• Then in a single query you can get User(s) which have role of isTeacher with their School

For eg: Say you have a table school_teachers which has columns for user_id, school_id (kind of pivot table) where each record can be uniquely identified by ['user_id', school_id'] composite key - unique index on database table.

Then you can define direct (many-to-many) relation between User and School

//User model
public function schools()
{
    return $this->belongsToMany(School::class, 'school_teachers', 'user_id', 'school_id');
}

//School model
public function teachers()
{
    return $this->belongsToMany(User::class, 'school_teachers', 'school_id', 'user_id');
}

In controller you can do

public function index()
{
    $teachers = User::with('schools')
        ->whereHas(
            'roles',
            fn($query) => $query->where('name', 'isTeacher')
        )
        ->get();

    return response()->json(['teachers' => $teachers]);
}

Laravel Docs - Eloquent Relationships - Many-to-Many