Using eloquent to query multiple tables

Question

I am using Laravel 5.6. I am trying to query information from the grading_info table but I also want to return the students name and other info from the student_info table. I only want to return records in the grading_info table that are related to the currently logged in teacher. Currently its returning information for all teachers. I know I can add a where clause but I am trying to learn eloquent and was wondering if there was any way to accomplish this?

Teachers and students can have many enteries in the grading_info table and any teacher can grade any student.

I would like to return something like this

{
    gradeID,
    gradeDate,
    gradeInfo
    .....
    student: {
        studentName,
        studentPhoneNumber,
        studentEmail
        ......
    }
}

users table (only stores teachers, not student)

id

teacher_info

teacherID (linked to id from users table)

student_info

id (auto increment. not relation to the users table)

grading_info

studentID (linked to id from student_info)
teacherID (linked to id from users)

User model

public function grades(){
    return $this->hasMany(GradingInfo::class, 'studentID');
}

GradingInfo model

public function teacher(){
    return $this->belongsTo(User::class, 'id', 'teacherID');
}

public function student() {
    return $this->belongsTo(StudentInfo::class, 'studentID', 'id');
}

StudentInfo model

public function grades() {
    return $this->hasMany(SessionInfo::class, 'studentID', 'id');
}

TeacherInfo model

// Nothing in here.

TeacherController

public function getGrades(Request $request)
{
    $user       = Auth::user(); // This is the teacher
    $grades     = $user->with('sessions.student')->orderBy('created_at', 'DESC')->get();
    return response()->json(['sessions' => $sessions], 200);
}

rkj · Accepted Answer · 2018-06-19 23:35:35Z

You have Many to Many relationship between user(teacher) and student(student_info) tables

User Model

public function gradeStudents(){
    return $this->belongsToMany(StudentInfo::class, 'grading_info', 'teacherID', 'studentID');
}

public function info(){  //get teacher info 
   return $this->hasOne(TeacherInfo::class, 'teacherID');
}

StudentInfo model

public function gradeTeachers(){
        return $this->belongsToMany(User::class, 'grading_info', 'studentID', 'teacherID');
}

Now Fetch the data (TeacherController)

public function getGrades(Request $request)
{
    $user     = Auth::user(); // This is the teacher
    $students = $user->gradeStudents; // it will return all graded students by logged in teacher 
    return response()->json(['students' => $students], 200);
}

Here grading_info is a pivot table for Many-To-Many relationship

for details check this https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

Fetch Extra info from pivot table

If you want to add extra info in pivot table (grading_info) then add column (info) in this table and then need to change relationship like this

public function gradeStudents(){
        return $this->belongsToMany(StudentInfo::class, 'grading_info', 'teacherID', 'studentID')
                    ->withPivot('info')
                    ->as('grade')
                    ->withTimestamps();              
}

Now if you fetch data

 $user = Auth::user(); // This is the teacher
 $students = $user->gradeStudents; 

 foreach($students as $student){
    print_r($student);
    print_r($student->grade->info);
    print_r($student->grade->created_at);
 }

Collectives™ on Stack Overflow

Using eloquent to query multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related