Ordering collection by relationship data

You may have to use Group by.

What is your method to access your concert info ?

@jlrdw tried that, but that's when I get an instance of Concert and not of Leg.

@ehben in mij Leg model I got:

Public function concerts() { Return $this->hasMany('App\Models\Concert'); }

@jlrdw You can't sort a model based on the relation! Laravel will always perform two queries to collect the parent and the children (relation) and therefore it's impossible to sort the results in the relation.

You need to use a join instead ;)

Problem is you want to sort by "earliest" concert date. So that's an extra sort. Maybe if you make a custom getEarliestDateAttribute() method or something on your leg model, you could do something along those lines:

$sorted = Leg::all()->sortBy(function ($leg, $key) {
    return $leg->earliest_date;
});

and then also

$sortedLegs = $tour->legs->sortBy(function ($leg, $key) {
    return $leg->earliest_date;
});

You have to use a join to order your result by another table date.

@bobbybouwman @pmall when joining I get a concert instance returned. Of course I could use loop through my "legs" (which results concerts) and go $concert->leg->someAttribute. But that seems very counterproductive and not very laravelish. I'm sure there's an easier way but not sure how. I'll post the exact code later today!

@prullenbak that will sort my concerts within my leg, I want to sort my legs.

@svdv22 No, it will sort your legs. Leg::all() is a collection of legs, which you can then sort based on the "earliest concert date", which you can get from your custom getter.

when joining I get a concert instance returned.

Select your legs and join the concert table.

@bobbybouwmann model or not you can sort mysql tables with group by and it's very fast and efficient see https://onedrive.live.com/redir?resid=FCCB050F947566C1!1199&authkey=!ANuLpA9dfKFKnd0&v=3&ithint=photo%2cjpg
Don't forget there are other possibilities other than eloquent, such as query builder.
Also see
http://dsmithweb.com/itemized.png and
http://laravel.io/forum/04-04-2015-looping-through-collection
Sometimes rolling up the sleeves and forming a regular long complex query is actually the answer.
Another good read:
http://laravel.io/forum/05-12-2015-has-many-through-relationship-depth

@jlrdw Dude serious? We are talking about relations and sorting the results based on the related items.

The only possible way to sort the tours based on the legs is using a join! Period.

Okay I see in the above case a join is necessary.

What am I doing wrong in my join then? This query:

$legs = DB::Table('legs')
             ->join('concerts', 'concerts.leg_id', '=', 'legs.id')
             ->orderBy('concerts.date', 'desc')
             ->groupBy('legs.name')
             ->paginate(25);

returns exactly what I want, but not as an instance of my Leg, but just a stdClass.. The problem with that is that direct data from the database works, like $leg->name, but appends that I made, like $leg->dateRange, return an error (Undefined property: stdClass::$dateRange).

Edit: and by appends I mean accessor. This is returning an error:

// Leg.php model definition
public function getDateRangeAttribute() {
    return true; // logic not important for this topic
}

// Query as written above here.
echo $leg->dateRange; // returns error

You can do :

Leg::with(['concerts' => function ($q) {
  $q->orderBy('concert_date');
}])->get();

If that's any help? (I've had a glass of pastis, so ymmv ;-)

Nope, tried that but no effect :) I'm pretty sure @bobbybouwmann is right and it has to be a join. I've seen him given that answer all over this forum, but I can't seem to make my accessor/append to work as well..

Okay, I got a little step closer to the nature of the problem.

$legs = Leg::all();
$legs->first()->concerts()->get(); // returns list of concerts
$legs->first()->id; // id of leg.. like I want it

$legs = Leg::join('concerts', 'legs.id', '=', 'concerts.leg_id')
             ->orderBy('concerts.date', 'desc')
             ->groupBy('legs.name')
             ->paginate(25);
$legs->first()->concerts()->get(); // returns no concerts
$legs->first()->id; // id of first concert - which explains there's no related concerts

That's because when you join two tables and end up with multiple columns with the same name MySQL returns the latest value.

I fixed this by adding ->select(['legs.*']) to my statement and omitting all concert data. Don't need it anyways.

Thank you all for thinking with me!