Laravel: How can I group records together using a collection?

Question

I am creating a stats application and I am trying to group athletes of the same rating together.

I have two tables: athletes and athlete_position. This is because any given athlete can play (be rated differently) for more than one position.

I am able to get a query that gets all of the records I am looking for, I am just hung up on grouping them together the correct way.

Here is how I am currently selecting the athletes:

MyController.php

public function ratings($year, $position_id) {
    $athletes = Athlete::where('graduation_year', $year)
        ->join('athlete_position', 'athlete_position.athlete_id', '=', 'athletes.id')
        ->where('athlete_position.position_id', '=', $position_id)
        ->groupBy('athlete_position.rating')
        ->orderBy('last_name')
        ->orderBy('athlete_position.rank')
        ->get();

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

This is working great. The next step I am trying to accomplish is to group all 5 star athletes together, all 4.5 star athletes together and so on.

So I am trying something like this:

 $collection = collect(
     Athlete::where('graduation_year', $year)
     ->join('athlete_position', 'athlete_position.athlete_id', '=', 'athletes.id')
     ->where('athlete_position.position_id', '=', $position_id)
 )->groupBy('athlete_position.rating');

I am sending the data to another application, and when I try to loop through the response:

{% for collection in athletes %}
    {% for athlete in athletes %}
        {{ athlete.first_name }}<br>
    {% endfor %}
{% endfor %}            

I get the following error: Key "first_name" for array with keys "0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10" does not exist.

Thank you for any suggestions!

Answer 1

Hopefully this will help someone else if they come across this thread. Here is the query that I got to work along with how I display the data in my view.

MyController.php

$collection = collect(
    $athletes = Athlete::where('athletes.graduation_year', $year)
        ->join('athlete_position', 'athlete_position.athlete_id', '=', 'athletes.id')
        ->where('athlete_position.position_id', '=', $position_id)
        ->orderBy('rank', 'asc')
        ->orderBy('rating', 'asc')
        ->get()
    )->groupBy('rating');


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

Then in my view (which is in another system) I can loop through the elements/groups (using twig) like this:

 ...
 {% if athletes|length %}
     {% for rating, athletes in athletes %}
         <div>{{ rating }}</div> // 5.0
          {% for athlete in athletes %}
              {{ athlete.last_name }}, {{ athlete.first_name }}  // Durst, Fred
              ...
          {% endfor %}
     {% endfor %}
 {% endif %}