php - Laravel - 连接四个表

Question

我有下表

Recipe:  
- id  
- name  
- image  

Ingredients:  
- id  
- title  
- image  

(pivot) recipes_ingredients  
- id  
- recipe_id  
- ingredients_id  

votes:  
- id  
- rating  
- recipe_id  

如何加入超过四张 table ？

我需要什么:
对于每种成分(其 flag == 1)，我需要 6 个随机食谱，其中也有投票。

喜欢
$ingredient->recipes在 FOREACH 然后 $recipe->rating (<-- AVG(ranking))

这是我的第一次尝试，但没有成功:

        $ingredients = Ingredients::with('recipes')
    ->where('ingredients.is_product',1)
    ->where('recipes.status', '=', '1')
    ->join('ingredient_recipe', 'ingredient_recipe.ingredients_id', '=', 'ingredients.id')
    ->join('recipes', 'ingredient_recipe.recipe_id', '=', 'recipes.id')
    ->select(array('ingredients.*'))
    ->get();  

基于此查询:

        $bestThreeRecipes = Recipe::with('user')
    ->where('recipes.status', '=', '1')
    ->join('votes', 'votes.recipe_id', '=', 'recipes.id')
    ->select(array('votes.*','recipes.*',DB::raw('AVG(rating) as ratings_average, COUNT(rating)')))
    ->groupBy('recipes.id')
    ->orderBy('ratings_average', 'DESC')
    ->get()->take(4);  

(这很好用!)

你有什么想法吗？谢谢你!

编辑＃1:
现在我尝试了这个:

        $ingerdients = Ingredients::with(array('recipes','recipes.votes'))
    ->where('ingredients.is_product',1)
    ->where('recipes.status', '=', '1')
    ->join('ingredient_recipe', 'ingredient_recipe.ingredients_id', '=', 'ingredients.id')
    ->join('recipes', 'ingredient_recipe.recipe_id', '=', 'recipes.id')
    ->select(array('ingredients.*'))
    ->get();  

但我仍然不知道如何让 AVG() 只为食谱工作!

编辑 #2 - 解决方案(目前):

这似乎有效，感谢 deczo!

        $ingredients = Ingredients::with(array(
      'recipes' => function ($q) {
        $q->where('recipes.status', '=', '1')
          ->join('votes','recipes.id','=','votes.recipe_id')
          ->select(DB::raw('avg(rating) AS rating'))->groupBy('recipes.id')->orderBy('rating', 'DESC');
      }))
        ->where('ingredients.is_product',1)
        ->get();

Answer 1

这应该可以解决问题:

$ingredients = Ingredients::with(array(
  'recipes' => function ($q) {
    $q->where('recipes.status', '=', '1')
      ->join(
        DB::raw('(SELECT AVG(rating) as rating, COUNT(rating) as ratingCount, id from votes group by recipe_id) as votes'), 
        'recipes.id', 
        '=', 
        'votes.recipe_id')
      ->select('votes.rating', 'votes.ratingCount');
  }))
    ->where('ingredients.is_product',1)
    ->get();