Getting multiple related models together

Question

In my Laravel app, I have three models, User, Course and CourseScore. I want to get all courses for an specific user with his score. something like:

{
  id: 1,
  name: first_name,
  corses: [
    {
      id: 1,
      name: 'course one',
      score: 17, // this is what i need for every course,
    },
    {
      id: 2,
      name: 'course two',
      score: 19, // this is what i need for every course,
    },
  ]
}
    

Here are my Models:

User

<?php
class User extends Authenticatable
{
    public function courseScores()
    {
        return $this->hasMany(CourseScore::class);
    }

    public function courses()
    {
        return $this->belongsToMany(Course::class);
    }
}

Course

<?php
class Course extends Model
{
    public function courseScores()
    {
        return $this->hasMany(CourseScore::class);
    }

    public function users()
    {
        return $this->belongsToMany(User::class);
    }
}

CourseScore

<?php
class CourseScore extends Model
{
    protected $table = 'course_scores';

    public function course()
    {
        return $this->belongsTo(Course::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

And of course, please let me know if the relations are incorrect.

Answer 1

If I understand it correctly, you can do something like this,

in your User model

  public function courses(){
    return $this->hasMany(Course::class); // changed belongsToMany to hasMany
  }

in your Course model

  public function users(){
    return $this->belongsTo(User::class); // changed belongsToMany to belongsTo
  }

In your controller

 $usersData = User::with('courses.courseScores')->get();
 dd($usersData);

I dont fully understand your table structure so Im assuming you have multiple row of Scores for single Course (One to Many). If the relationship is one is to one then add in your Course Model

public function courseScores(){
    return $this->hasOne(CourseScore::class); //changed hasMany to hasOne
}

Answer 2

You can try using this

   $val = DB::table('course as c')
        ->join('user as u', 'u.id','c.id')
        -join('courseScore as cs','u.id','cs.id')
        ->get();

Then just dd($val) to see what value it shown