How to render data from a different table in hasMany relationship Laravel?

Question

On a jobs site I'm making I have a table for users, candidate_skills and skills. A user has many candidate_skills. Each candidate_skill belongs to a user and also belongs to a skill.

The candidate_skills table has these columns:

id
user_id 
skill_id
years_xp
comfort_level
created_at
updated_at

The skills table has an

id
name
...

Each user can pick a skill from from the skills table. That record gets added as a candidate_skill.

I am rendering a list of users and I'd like to render the skills that each candidate has. On my user model I have a method to grab a user's candidate_skills:

public function skills()
{
   return $this->hasMany('App\CandidateSkill');
}

How do I populate this list with the names of the skills from the skills table?

Right now I'm grabbing the results like:

$candidates = User::whereIn('department_id', $departmentFilters)
            ->whereIn('work_authorization', $workAuthFilters)
            ->orderBy('updated_at', 'desc')
            ->with('skills')
            ->get();

return response()->json($candidates);

This loads the list of candidate_skills but doesn't include a readable name for what the skill is. The result of the above query gives $candidate->skill like:

{ "id": 22, "user_id": 43, "skill_id": 64, "years_xp": 2, "comfort_level": null, "created_at": "2019-04-15 18:51:14", "updated_at": "2019-04-15 18:51:14" }

How can I include the name of the skill when rendering the candidate_skills for a user?

For example, skill with id of 64 has a name "PHP". When displaying user 43 I'd like to list out their skill with the name "PHP" instead of 64.

score 2 · Accepted Answer · edited May 06 '19 at 13:35

User model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\Skill;

class User extends Model
{
    /**
     * The skills that belong to the user.
     */
    public function skills()
    {
        return $this->belongsToMany(Skill::class, 'candidate_skills', 'user_id', 'skill_id' )->withPivot('years_xp', 'comfort_level');
    }
}

and Skill model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\User;

class Skill extends Model
{
    /**
     * The users that belong to the user.
     */
    public function users()
    {
        return $this->belongsToMany(User::class, 'candidate_skills', 'user_id', 'skill_id' )->withPivot('years_xp', 'comfort_level');
    }
}

Now, to get users with department_id and work_authorization filters

$candidates = User::whereIn('department_id', $departmentFilters)
            ->whereIn('work_authorization', $workAuthFilters)
            ->orderBy('updated_at', 'desc')
            ->with('skills')
            ->get();

and while displaying in blade

@foreach($candidates as $candidate)
    @foreach ( $candidate->skills as $skill )
        {{ $skill->name }}
    @endforeach
@endforeach

or

@foreach($candidates as $candidate)
    @foreach ( $candidate->skills as $skill )
        {{ $skill->pivot->years_xp}}
        {{ $skill->pivot->comfort_level}}
    @endforeach
@endforeach

I have no demo data to test it, but I guess it will work fine.

Awesome I had not used a pivot table before. Works great! Thank you — Connor Leech, May 06 '19 at 13:35

score 0 · Answer 2 · answered May 03 '19 at 20:53

0

Many to many relationships in laravel need both models to have belongsToMany. By default laravel will look for the candidate_skills table and will expect a candidate_id and skill_id column.

https://laravel.com/docs/5.8/eloquent-relationships#many-to-many

Candidate model

function skills() {
    return $this->belongsToMany(Skill::class);
}

Skill model

function candidates() {
    return $this->belongsToMany(Candidate::class);
}

Then you should be able to grab either model and get the related data.

$skills = Skills::with('candidates')->get();
foreach($skills as $skill) {
    foreach($skill->candidate as $candidate) { 
        echo $skill->candidate  
    }
}

answered May 03 '19 at 20:53

FunkyMonk91

1,469
1
17
30

When I try that I get error `Column not found: 1054 Unknown column 'skills.user_id'`. The skills table does not have a user id column. I have the user_id on the candidate_skills table – Connor Leech May 03 '19 at 22:25
You will need to access the pivot table, `$skill->pivot->user_id` or `$skill->user->id` – FunkyMonk91 May 04 '19 at 12:10

How to render data from a different table in hasMany relationship Laravel?

2 Answers2

Candidate model

Skill model