3 Foreign keys in a pivot Table with extra data

Hi guys, I am having a hard time figuring this out or even if this is necessary. I have 3 Models that each have a(as I believe) belongs to many relationship. But I can't figure out if it is possible to create the relationship through eloquent.

/*
Tables:
    recipes
        id
        title

    products
        id
        title

    measurement_units
        id
        title

    recipe_ingredients
        id
        recipe_id
        product_id
        measurement_unit_id
        amount
*/
Class Recipe extends Model {
    // Here I want to have a method that retrieves the ingredients from the pivot table
    public function ingredients()
    {
         /* Here I expect to retrieve a Collection that would have this data
              ingredient_id
              product_title
              measurement_unit_title
              amount
		  */
    }
}
}
Class Product extends Model {

}
Class MeasurementUnit extends Model {
}

Is this possible or should I just write my own SQL query? Thanks

idew

4 years ago

Level 26

I think in this scenario it'd be best to create a model for the pivot table:

class RecipeIngredient extends Model {
		public function product() {
				return $this->belongsTo(Product::class);
		}
		public function measurementUnit() {
				return $this->belongsTo(MeasurementUnit::class);
		}
}
class Recipe extends Model {
		public function ingredients() {
				return $this->hasMany(RecipeIngredient::class);
		}
}
...
// Referencing single recipe ingredient
$ingredient->product->title;
$ingredient->measurement_unit->title;

Or if you wanted to reference them exactly as you describe above:

class Recipe extends Model {
		public function ingredients() {
				return $this->hasMany(RecipeIngredient::class)
						->join('products', 'recipe_ingedients.product_id', 'products.id')
						->join('measurement_units', 'recipe_ingredients.measurement_unit_id', 'measurement_units.id')
						->select('recipe_ingredients.*', DB::raw('products.title as product_title'), DB::raw('measurement_units.title as measurement_unit_title'));
		}
}

1 like

marius.slovikas

4 years ago

Level 5

@idew Yeah, I believe having a model for the pivot table is the best approach, thanks

1 like

Please or to participate in this conversation.