Laravel BelongsToMany with two pivot table

0
votes

I have the following table structure:

products

  • id

product_formats

  • id
  • product_id

product_prices

  • id
  • product_format_id
  • market_id

markets

  • id

A product can have multiple formats, with each having their own price which belongs in a different market. How can I retrieve the list of Markets from the Product model?

I used to have a single pivot table, however now I have two pivot.

class Product extends Model
{
    public function markets()
    {
        return $this->belongsToMany(Market::class);
    }
}

Update

To get the result I want, I did the following:

public function markets()
{
    return Market::whereIn('id', $this->prices()->distinct()->pluck('market_id')->toArray());
}

However, I'm wondering if there's a way to accomplish this via a relationship.

1
laravellaravel-5
Simple. Just do a one to many relation between products and product_formats and a many to many relation between product_formats and markets - Luis felipe De jesus Munoz
Then if you want to retrieve the list of Markets from one product just do $product->product_format[0]->markets. I said format[0] because of the one to many relation - Luis felipe De jesus Munoz
@LuisfelipeDejesusMunoz Wouldn't that retrieve the markets of my first format only? I want all the unique markets of each formats. - Chin Leung
It depends in how you query your data. if you want all the unique markets of a single product you can do something like markets->whereHas("product_formats.products", function($query){$query->where("id", myProductId);})->get() - Luis felipe De jesus Munoz

1 Answers

0
votes

You need to build relationships in models.

ProductModel:

public function productFormats()
{
  return $this->belongTo(ProductFormatsModel::class);
}

ProductFormatsModel:

public function productPrices()
{
  return $this->belongTo(ProductPricesModel::class);
}

ProductPricesModel:

public function markets()
{
  return $this->hasOne(MarketsModel::class);
}

in Controller:

foreach($product->productFormats as $productFormat) 
{
  foreach($productFormat->productPrices as $productPrice)
  {
     var_dump($productPrice->markets);
  }
}

For unique markets

in ProductModel:

public function productPrices()
    {
        return $this->hasManyThrough(
                      'App\ProductPricesModel', 
                      'App\ProductFormatsModel',
                      'product_id', 
                      'product_format_id'
                      'id',
                      'id'
                     );
    }

in Controller

foreach($product->productPrices as $productPrice)
{
  var_dump($productPrice->markets)
}