Laravel many to many selecting with Eloquent

Question

I have 2 tables, users and skills, and a many to many relationship between them. Pivot table skill_user also has column 'level' - at what level this user knows this skill.

in User model I have:

public function skills(){
    return $this->belongsToMany('Skill')->withPivot('level');
}

and in Skill model:

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

Now, I need to run a query which will return something like this:

"Select all users who has skill_id_1 > 40 && skill_id_2 > 55 && skill_id_3 > 67"

where skill_ids are different skills, and each user returned should have each of the given skill at required level.

I searched in Laravel documentation and in stackoverflow as much as I could but couldn't find the solution. Any help will be appreciated, thanks!

You should probably explain it better. What tables you have and how you get skill_id_1. It's not very clear at the moment — Marcin Nabiałek, Dec 28 '14 at 10:15
skill_id_1 and skill_id_2 are just variables, there will be actual id's, for example: "Select all users who has php(with id=1) > 40 && mysql(id=2) > 55" — Michael Abramishvili, Dec 28 '14 at 11:04

score 5 · Accepted Answer · answered Dec 28 '14 at 11:13

What you need is whereHas. Multiple of them.

$users = User::whereHas('skills', function($q){
    $q->where('skill_id', 1);
    $q->where('level', '>', 44);
})->whereHas('skills', function($q){
    $q->where('skill_id', 2);
    $q->where('level', '>', 55);
})->whereHas('skills', function($q){
    $q->where('skill_id', 3);
    $q->where('level', '>', 67);
})->get();

I'm assuming the values are dynamic so this should simplify it a lot:

$requirements = array('1' => 40, '2' => 55, '3' => 67);

$users = User::query();
foreach($requirements as $id => $value){
    $users->whereHas('skills', function($q) use ($id, $value){
        $q->where('skill_id', $id);
        $q->where('level', '>', $value);
    });
}
$users = $users->get();

Laravel many to many selecting with Eloquent

1 Answers1