CODEWITHSUNDEEP
phpmysqllaraveleloquent
Kay
Kay

Reputation: 61

Laravel / Eloquent: nested WhereHas

I've just started learning Laravel and stumbled upon one issue which I can't make work using the Eloquent relationships.

Let's assume that I have a Worker model, a Skill model and a pivot table skills_workers to keep the many-to-many relationship. When I'm trying to get all the workers, who have following skill, then it goes without a problem using the following syntax:

    $skill='carpenter';

    $workers=Worker::whereHas('skills', function (Builder $query) use($skill){
            $query->where('name','=',$skill);
    })->get()();

The problem begins when I need to find all workers who have the set of skills. For example, carpenter-driver-chef (just for example). If the worker should have one of the skills, then I'd just use the whereIn function, but I need to find the worker who posess all of the skills in array. I can't make the nested WhereHas as every time the user performs a search the skill set might be different. Sometimes it's just 1 skill, sometimes 2 and so on. So the following construction:

    $skills=['carpenter','driver'];

    $workers=Worker::whereHas('skills', function (Builder $query) use($skills){
            $query->where('name','=',$skills[0]);
    })->whereHas('skills', function (Builder $query) use($skills){
        $query->where('name','=',$skills[1]);
    })->get();

is not an option.

Is there a way to use whereHas inside of a foreach loop, for example? Or, maybe, there is a more elegant way of performing such queries? None of the other posts on StackOverflow that I've found, helped... I'd really like to avoid using the raw SQL queries, if possible. Thank you in advance

Upvotes: 1

Views: 609

Answers (3)

Peppermintology
Peppermintology

Reputation: 10210

As your $skills variable appears to be an array, you could use the Eloquent whereIn function.

$workers = Worker::whereHas('skills', function (Builder $query) use ($skills) {
    $query->whereIn('name', $skills);
})->get();

Update

The following should get you a collection of Workers that have all the Skills.

$workers = Worker::whereHas('skills');

foreach ($skills as $skill) {
    $workers->whereHas('skills', function (Builder $query) use ($skill) {
        $query->where('name', $skill);
    })->get();
}

$workers->get();

Upvotes: 1

Yves Kipondo
Yves Kipondo

Reputation: 5603

You can start with getting all skills and after that you can use whereIn like this

$skills=['carpenter','driver'];

$skills_id = Skill::whereIn(['name',$skills])->pluck('id');

By using pluck the query will return an array of IDs [1,3,...] not model.

$workers = Worker::whereHas('skills', function(Builder $query) use ($skills_id) {
    $query->whereIn('id', $skills_id);
})->get();

Upvotes: 0

John Lobo
John Lobo

Reputation: 15319

I think you can use foreach for skills to get multiple matching condition

   $workers=Worker::whereHas('skills', function (Builder $query) use($skills){
               foreach($skills as $value){
            $query->where('name',$value);
         }
                
    
        })->get();

Upvotes: 0

Related Questions