CODEWITHSUNDEEP
phpsortinglaraveleloquent
lemonaida
lemonaida

Reputation: 128

Eloquent Sorting and Grouping on Relationship

I have three tables simplified like this:

 People (id, first_name, last_name)
 Awards (id, name)
 AwardPerson (id, people_id, award_id, year_gained)

I need to display this on a page, with awards sorted by year, descending. I would ideally like to have a collection or array like this:

array(
     '2005' => array(
         'AwardPerson' => array(
             'person' => array(person data...),
             'award'  => array(award data...)
          ),
          'AwardPerson2' => array(
              'person' => array(person data...),
              'award'  => array(award data...)
          ),
      )
      '2004' => array(...

Currently I have my query set up like this:

$years = AwardPerson::whereHas('person', function($q)
          {
            $q->current() //get only people with current appoitnments
          })
          ->with('person', 'award')
          ->orderBy('year_gained', 'desc')
          ->get();

$years = $years->groupBy('year_gained');

And this works out fine, except I need to be able to sort each AwardPerson by person->last_name. I've tried looping, through and sorting after grouping by year:

$years->each(function($year)
    {
      usort($year, function($a, $b)
      {
        if($a->person->last_name == $b->person->last_name)
        {
          return 0;
        } 
        return ($a->person->last_name < $b->person->last_name) ? -1 : 1;
      });
    });

But that seems to make no difference to the order. I also know that I should probably be doing this query with a pivot table, but I can't figure out how to get the data in the right format that way.

Upvotes: 1

Views: 73

Answers (1)

blablabla
blablabla

Reputation: 1478

What about just what you had, but then add a join with people, like this.

$years = AwardPerson
      ->select('AwardPerson.*')
      ->whereHas('person', function($q)
      {
        $q->current() //get only people with current appoitnments
      })
      ->join('people', 'people.id', '=', 'AwardPerson.id')
      ->with('person', 'award')
      ->orderBy('year_gained', 'desc')
      ->orderBy('people.last_name', 'ASC')
      ->get();

$years = $years->groupBy('year_gained');

Upvotes: 1

Related Questions