CODEWITHSUNDEEP
phpmysqlsqllaravellaravel-4
Marek123
Marek123

Reputation: 1211

Laravel - Join over four tables

I have the following tables 

Recipe:  
- id  
- name  
- image  

Ingredients:  
- id  
- title  
- image  

(pivot) recipes_ingredients  
- id  
- recipe_id  
- ingredients_id  

votes:  
- id  
- rating  
- recipe_id

How can I join over four tables?

What I need:
For each ingredient (which has flag == 1), I need 6 random recipes, which also have the votes in it.

Like
$ingredient->recipes in a FOREACH and then $recipe->rating (<-- AVG(ranking))

This was my first attempt, but didn't work: 

        $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();

based on this query: 

        $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);

(This works fine!)

Do you have an idea? Thank you!

EDIT #1:
Now I tried this: 

        $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();

But I still don't know how to get the AVG() working just for the recipes!

EDIT #2 - Solution (for now):

This seems to work, thanks to 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();

Upvotes: 0

Views: 205

Answers (1)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81147

This should do the trick:

$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();

Upvotes: 1

Related Questions