Just for reference I am using Laravel 5.
I have a two tables
users
id
first name
skills
id
name
and a pivot table
skill_user
skill_id
user_id
if I do a select in MySQL as follows:
select users.id as id, users.first_name, skills.name from users
left join skill_user on users.id = skill_user.user_id
left join skills on skill_user.skill_id=skills.id
I get:
id, first_name, skill
1, Jenna, Reliable
1, Jenna, Organized
2, Alex, Hardworking
3, Barry, Capable
3, Barry, Amiable
3, Barry, Patient
4, Janine, (null)
I pass this through to a view via a Controller:
$peoples = [];
$peoples = \DB::table('users')
->select(\DB::raw('users.id as id, first_name, skill.name as name"'))
->leftJoin('skill_user','users.id','=','skill_user.user_id')
->leftJoin('skills','skill_user.skill_id','=','skills.id')
->get();
return view('find-people', compact(['peoples']));
Now, I want to loop through this in the view (pseudocode):
forelse ( peoples as people )
people - > first_name
people - > skill
empty
no people found
endforelse
Which all works fine in a sense - but the first name gets repeated when there is more than one skill.
I can probably hack a loop of the skills by doing something like comparing user_id to itself but it seems such a clumsy way to do it.
user_id = $peoples->id
while (some looping criteria)
{
write out skills
if($peoples->id != user_id){break;}
}
How do I loop through the recordset in an elegant/eloquent fashion? Or is there a better entirely to do this?
If you define the relationships in your models you don't need to try and construct raw SQL to achieve that. This is what the Laravel ORM "Eloquent" is for!
class People extends Model {
public function skills () {
return $this->hasMany('Skill');
}
}
Then you define the skill model :
class Skill extends Model {
public function People () {
return $this->belongsToMany('People');
}
}
Now you're able to iterate over the People model and for each person, get their ->skills. This way you don't end up with the duplicate issue you're experiencing and you greatly simplify the SQL you're trying to achieve by leveraging the ORM.