Using Laravel How can I return a list of schools for each athlete?

I am trying to include a list of schools for each athlete in my query.

This is what my query looks like right now.

$athletes = collect(
    DB::table('athletes')->select('id', 'first_name', 'last_name', 'height_ft', 'height_in', 'weight', 'high_school_state', 'graduation_year', 'rank', 'rating', 'evaluations.comments', 'offers.school as committed')
            ->where('athletes.graduation_year', $graduation_year)
            ->join('athlete_position', 'athlete_position.athlete_id', '=', 'athletes.id')
            ->where('athlete_position.position_id', '=', $position_id)
            ->join('evaluations', 'evaluations.athlete_id', 'athletes.id')
            ->whereNotNull('evaluations.comments')
            ->where('evaluations.status', 'published')
            //->join('offers', 'offers.athlete_id', 'athletes.id')
            //->where('offers.committed', 1)
            ->orderBy('rank', 'asc')
            ->orderBy('rating', 'asc')
            ->orderBy('last_name', 'asc')
            ->get()
    )->groupBy('rating');

    return response()->json(['data' => $athletes], 200);

When I comment out the offers, I get all athletes that I am looking for.

{
    "data": {
        "5.0": [
            {
                "id": 123,
                "first_name": "First",
                "last_name": "Name",
                ...
             }
        ]
     }
}

Each athlete an have many offers. So, when I un-comment offers I get the same athlete returned for however many offers they have:

{
    "data": {
        "5.0": [
            {
                "id": 123,
                "first_name": "First",
                "last_name": "Name",
                "committed": "School One"
             },
             {
                "id": 123,
                "first_name": "First",
                "last_name": "Name",
                "committed": "School Two"
             },
        ]
     }
}

I still get all athletes back which is good, but now there are duplicates.

If I un-comment offers.committed, then I only get athletes who have offers with a status of committed. That's not good, I still want all athletes even if they don't have an offer with a status of committed.

This is the type of response I am trying to generate:

{
    "data": {
        "5.0": [
            {
                "id": 123,
                "first_name": "First",
                "last_name": "Name",
                "committed:  "School One"
             },
             {
                "id": 456,
                "first_name": "First",
                "last_name": "Name",
                "committed": "School Two"
             },
             {
                "id": 789,
                "first_name": "First",
                "last_name": "Name",
                "committed": ""
             },
        ]
     }
}

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2laYqtU
via IFTTT