Filtering an eager loaded relationship on another relationship

I'm having an issue finding a way to make this work.

I have 3 tables. Equipment, Locations, Rates

Each equipment has a rate as well as a possibility for a rate that has an additional discount_code from the location the equipment is.

I've set up two relationships for this, BaseRate and LocationRate. They are both simple relationships on the id=foreignkey. My goal was to filter the LocationRate using the equipment's location's discount_code in the eager loading query, like so:

Equipment::with(['location', 
                 'baseRate',
                 'locationRate' => function($query) {
                                 $query->where('discount_code', [????]);
                             }]);

The [????] bit is where I'm unsure how to access the location's discount property. I understand that this may be a completely wrong way to do this kind of filtering.

Any help would be greatly appreciated.

Snapey

9 years ago

Level 122

You probably need to get the discount code first, then pass it into the function with use

Equipment::with(['location', 
                 'baseRate',
                 'locationRate' => function($query) use ($discount) {
                                 $query->where('discount_code', $discount);
                             }]);

1 like

Shobo

9 years ago

Level 1

Unfortunately that will not work as each equipment could have a different location and therefore a different discount code.

The only way I've found this to work is like so,

foreach($equipment as $eq) {
    if(!empty($eq->location->discount_code)) {
      $eq->load(['locationRentalRate' => function($query) use ($eq) {
        $query->byDiscountCode($eq->location->discount_code);
      }]);
    }
}

This will work as using load after the rows have already been fetched I have access to each equipment's location. However... I don't know how to do this in a single call as the above would result in a separate database call for each equipment record.

Please or to participate in this conversation.