Deep relations, *child* relation depends on *great-grandparent* id - with

I have this DB/Model structure (example):

*Food Category ------------ (id, name) - *irrelevant

Foods ---------------------- (id, food_category_id, name, ...)

Accessory Groups --------- (id, name, ...)

Foods_Accessory Groups - (food_id, acc_group_id) - pivot table

Accessories ---------------- (id, acc_group_id, name, ...)

Foods_Accessories_Prices - (food_id, accessory_id, price) - pivot table

Models

FoodCategory (hasMany: foods)
Food (hasMany: foodAccessoriesGroup)
FoodAccessoriesGroup (hasMany: foodAccessory)
problematic: *FoodAccessory (belongsToMany | belongsTo: food_prices | food_price ------ (pivot: [price], table: food_accessory_prices, model: Food)

Test:

**foods**
| id       | name |
| -------- | ---- |
| 1        | Pork |
| 2        | Eggs |

**accessory_groups**
| id     | name |
| -------| -----|
| 1      | Sauce|

foods_accessory_groups
| food_id  | accessory_group_id |
| -------- | --------------     |
| 1        | 1                  |
| 2        | 1                  |

**accessories**
| id       | name           | acc_group_id |
| -------- | -------------- | - |
| 1        | Mayo           | 1 |
| 2        | Ketchup        | 1 |

**foods_accessories_prices**
| food_id  | accessory_id   | price |
| -------- | -------------- | -     |
| 1        | 1              | 200   |
| 2        | 1              | 300   |
| 1        | 2              | 250   |
| 2        | 2              | 350   |

Code Example:

// get all food and relations
$foodCategories = FoodCategory::with([
  'foods',
  'foods.accessory_groups',
  'foods.accessory_groups.accessories',
  'foods.accessory_groups.accessories.food_prices' or
  'foods.accessory_groups.accessories.food_price' => function ($query) {
    // how to filter food prices by great-grandparent relation food (by food_id)
    // I know that it can't be done here, but where and how
  }
])->get();

Accessory dynamic price is depended on Food (great-grandparent relation). What is best practice and most optimize solution for this problem? Post-process or Laravel Eloquent has solution without too much trouble

food (id = 1, Pork) -> has foods_accessory_groups(id = 1, Sauce) -> has accessories (mayo and ketchup) where mayo price is 200, and ketchup price is 300.

food (id = 2, Eggs) - ... - mayo price is 250, and ketchup price is 350.

CodePudding user response：

You could use the whereHas method to query only FoodCategory models that have the relationship with the conditions you specify.

$foodCategories = FoodCategory::query()
    ->with([
        'foods',
        'foods.accessory_groups',
        'foods.accessory_groups.accessories',
        'foods.accessory_groups.accessories.food_prices' or
        'foods.accessory_groups.accessories.food_price' => function ($query) {
            // load only models from that relationship that match the condition(s)
            $query->where(...)
        }
    ])
    ->whereHas('foods.accessory_groups.accessories.food_price', function ($query) {
        // get only categories that have a nested relationship with condition(s)
        $query->where(...)
    })
    ->get();

CodePudding user response：

You can directly access the accessories with prices on your Foods model like this

public function accessories() {
    return $this->belongsToMany( Accessory::class )->withPivot('price');
}

or do the reverse inside your Accessories Model

public function foods() {
    return $this->belongsToMany( Food::class )->withPivot('price');
}

but since you are not querying the food model directly you need to properly define the relationship for each model up until your FoodCategory model