Laravel - Retrieve pivot relationship

Question

I have a following database schema:

users
    id

skills
    id

skill_user
    id
    user_id
    skill_id

endorsements
    id
    skill_user_id

The Laravel models are defined as following:

class User extends Authenticatable
{
    public function skills()
    {
        return $this
            ->belongsToMany('App\Skill')
            ->using(SkillUser::class)
            ->withPivot('endorsements_count')
            ->orderBy('endorsements_count', 'desc');
    }

}

class Skill extends Model
{
    public function users()
    {
        return $this
            ->belongsToMany('App\User')
            ->using(SkillUser::class)
            ->withPivot('user_id');
    }
}

class SkillUser extends Pivot
{
    public function endorsements()
    {
        return $this->hasMany('App\Endorsement', 'skill_user_id', 'id');
    }
}

class Endorsement extends Model
{
    public function skill_user() {
        return $this->belongsTo('App\SkillUser');
    }
}

From the above, you can see that the user can have multiple skills joined through a many-to-many table called SkillUser. Further, each user's skill can be endorsed by other users (endorsement). The endorsements are joined on the pivot table (each user_skill can have multiple endorsements).

Now I am trying to figure out how to construct a query in Laravel (Eloquent) that will fetch a users with all his skills and for each skill, all it's endorsements in a SINGLE query.

When I do following:

User::with('skills')->findOrFail(1)->skills[0]->pivot->endorsements

It correctly returns the user and the skill information, but the endorsements inside of the pivot (UserSkill) are empty. Also when I inspect the pivot->relations, it is also empty.

On the other hand, if I query like this:

SkillUser::where('id', '=', 1)->first()->endorsements

I get all the endorsements correctly so I guess the relationship is set-up correctly. I think I am missing to somehow include the 'endorsements' specifically in the pivot table.

Any help would be greatly appreciated!

Answer 1

class Skill extends Model
{
    public function endorsements()
    {
        return $this->hasMany(Endorsement::class, 'skill_user_id');
    }
}

class Endorsement extends Model
{
    public function user()
    {
        return $this->hasOne(User::class, 'id', 'user_id')->select(['id', 'firstname', 'lastname']);
    }
}

class User extends Model
{
    public function skills()
    {
        return $this->belongsToMany(Skill::class)->using(SkillUser::class)->select(['skill_user.id']);
    }
}

I added endorser column to endorsements table to associate with user.

return User::with('skills.endorsements.user')->findOrFail(1, ['id', 'name']);

it prints something like this;

{
  "id": 1,
  "name": "my-user",
  "skills": [
    {
      "id": 1,
      "name": "askill",
      "pivot": {
        "user_id": 1,
        "skill_id": 1
      },
      "endorsements": [
        {
          "id": 1,
          "skill_user_id": 1,
          "endorser": 2,
          "user": {
            "id": 2,
            "name": "user-2"
          }
        },
        {
          "id": 2,
          "skill_user_id": 1,
          "endorser": 3,
          "user": {
            "id": 3,
            "name": "user-3"
          }
        }
      ]
    },
    {
      "id": 2,
      "name": "bskill",
      "pivot": {
        "user_id": 1,
        "skill_id": 2
      },
      "endorsements": [
        {
          "id": 3,
          "skill_user_id": 2,
          "endorser": 2,
          "user": {
            "id": 2,
            "name": "user-2"
          }
        }
      ]
    },
    {
      "id": 4,
      "name": "dskill",
      "pivot": {
        "user_id": 1,
        "skill_id": 4
      },
      "endorsements": []
    }
  ]
}

here are the queries executed behind the scenes;

SELECT `id`, `name`
FROM `users`
WHERE `users`.`id` = '1'
LIMIT 1;

SELECT `skills`.*, `skill_user`.`user_id` as `pivot_user_id`, `skill_user`.`skill_id` as `pivot_skill_id`
FROM `skills`
         inner join `skill_user` on `skills`.`id` = `skill_user`.`skill_id`
WHERE `skill_user`.`user_id` in ('1');

SELECT *
FROM `endorsements`
WHERE `endorsements`.`skill_user_id` in ('1', '2', '4');

SELECT `id`, `name`
FROM `users`
WHERE `users`.`id` in ('2', '3');