在记录集的循环中循环

Question

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?

Answer 1

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.