Laravel - Relationship missing data

Question

I have a very strange problem with my many to many relationship.

This function:

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

Should get me all Ingredients and the recipes for it. Problem is, some recipes for an ingredient are missing.

The Join isn't working.
I added recipe_id = 7 to ingredient_id = 1 and = 2 and it just displays this recipe in ingredient_id = 1 but not in 2.
If I change the primary ID in the pivot (let's say ingredient = 1 was on 1 and 2 was on 2) and I change ingredient_ID 1 on 2 and vice verca, the recipe with 7 is in ingredient_id = 2 but not in 1 anymore.

But it should be in both ingredients. Why is that so? It's like the join thinks "Ok, I added it already to the id before, so skip it for this id".

My Models:

Recipe.php

    public function ingredients() {
        return $this->hasMany('Ingredients','ingredients_recipe','recipe_id','ingredients_id')->withPivot('amount');
}

Ingredients.php

    public function recipes() {
        return $this->belongsToMany('Recipe','ingredients_recipe','ingredients_id','recipe_id')->withPivot('amount');
}

EDIT #1:
Ok, i figured out, that if I delete the function() for the votes, it works fine. What's happening there? I need a rating for each recipe.

Marek123 Marek123 · Accepted Answer · 2014-04-18T00:44:43

Got it:

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

Apparently I had to add recipes.votes into the array and join on that instead on recipes with votes. Now I join on recipes and with(recipes.votes). This works! Strange!

Laravel - Relationship missing data

1 Answers