How to get searched data from second model through first one in Laravel?

user_9691242 0
Viewed 74

The Profile model with (id | user_id | name) columns has user relation as:

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

the User model has skills relation as:

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

the pivot table is skill_user with user_id | skill_id columns and Skill model user relation as:

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

How I can search for profiles that have a specific skill from the search request.

$profiles = Profile::with('users')->where('name', 'like', ''.$term.'%')->get();

but I get an error of not profile_skill table!

I can reach to each profile that has a specific skill through

$profile->user->skills

but how I can reach to these skills from :

<input name="term" type="text" />

I have tested users relation in Profile model but I get error :

    public function users(){
        return $this->hasManyThrough(User::class, Skill::class, 'skill_id', 'user_id');
    }
laravel
2 Answers

In addition, you might want to try whereHas, which I believe is the simplest:

return Profile::whereHas('user.skills', function ($query) use ($term) {
    $query->where('name', 'like', "{$term}%");
})
->get();
user_29949200

There's 2 ways to do this. The first one is to use eager loaded relationships and filter the resulting collections. The code is much more straight forward in this manner, but less efficient. This will check for EXACT matches, no LIKE queries. If you need LIKE please refer to option 2.

$profiles = Profile::with('user.skills')->get();

$profilesWithSkill = $profiles->filter(function ($profile, $key) use($term) {
    return $profile->user->skills->contains('name', $term);
});

The second approach is to use query builder and joins. This should be faster but will return associative arrays instead of model instances. You can customise the select for the fields you need.

DB::table('profiles')
    -> join('skill_user', 'skill_user.user_id', '=', 'profiles.user_id')
    -> join('skills', 'skills.id', '=', 'skill_user.skill_id')
    -> where('skills.name', 'like', ''.$term.'%')
    -> select('profiles.*')
    -> get();

It highly depends on your use case which one you should go for, and it essentialy matters when you get to high amounts of data.

user_115005350
Related