Laravel Eager Load and Group Multiple Joins on Pivot

Question

I have a Pivot table thats used to join two other tables that have many relations per hotel_id. Is there a way I can eagerload the relationship that pulls the results for both tables in one relationship? The raw SQL query, works correctly but when using belongsToMany the order is off.

Amenities Pivot Table

id
hotel_id 
distance_id
type_id

Distance Table

id
name

Type Table

id
name

RAW Query (This works fine)

SELECT * FROM amenities a 
LEFT JOIN distance d ON a.distance_id = d.id 
LEFT JOIN type t ON a.type_id = t.id WHERE a.hotel_id = ?

My "Hotels" Model is using belongsToMany like so

public function distance() {
    return $this->belongsToMany('Distance', 'amenities', 'hotel_id', 'distance_id');
}
public function type() {
    return $this->belongsToMany('Type', 'amenities', 'hotel_id', 'type_id');
}

This outputs the collection, but they are not grouped correctly. I need to loop these into select fields side by side as entered in the pivot table, so a user can select a "type" and the "distance", but the order is off when using the collection. The raw query above outputs correctly.

Hotels::where('id','=','200')->with('distance', 'type')->take(5)->get();

Ok Solved it. So apparently you can use orderBy on your pivot table. Incase anyone else has this issue this is what I did on both relationships.

public function distance() {
    return $this->belongsToMany('Distance', 'amenities', 'hotel_id', 'distance_id')->withPivot('id')->orderBy('pivot_id','desc');
}
public function type() {
    return $this->belongsToMany('Type', 'amenities', 'hotel_id', 'type_id')->withPivot('id')->orderBy('pivot_id','desc');
}

Collin James Collin James · Accepted Answer · 2015-06-03T17:33:39

It's not really a great practice to include other query building steps in the relationship methods on your models. The relationship method should just define the relationship, nothing else. A cleaner method is to apply eager load constraints. (scroll down a bit) Consider the following.

Hotels::where('id', 200)->with(array(
    'distance' => function ($query)
    {
        $query->withPivot('id')->orderBy('pivot_id','desc');
    },
    'type' => function ($query)
    {
        $query->withPivot('id')->orderBy('pivot_id','desc');
    },
))->take(5)->get();

If you find that you are eagerly loading this relationship in this way often, consider using scopes to keep things DRY. The end result will allow you to do something like this.

Hotels::where('id', 200)->withOrderedDistance()->withOrderedType()->take(5)->get();

P.S. Your models should be singular. Hotel, not Hotels. The model represents a single record.

Laravel Eager Load and Group Multiple Joins on Pivot

2 Answers