The problem

I've got three tables, shops, products, and product_shopas the pivot table. The Shop and Product are related using a belongsToMany (Many to Many). When inserting a new Product into the database, in my form I can specify a price for each Shop. When submitting the form, the product_id and shop_id's are being inserted into the Pivot table with their related prices.

我有三个表，shop、products和product_shopas作为数据透视表。商店和产品都与使用归属感相关(很多)。当向数据库中插入新产品时，在我的表单中，我可以指定每个商店的价格。提交表单时，将把product_id和shop_id及其相关价格插入到Pivot表中。

My question is, how do I retrieve only the price of a product from the pivot table, when specifying a shop_id? Ultimately, my goal is described below, and there might be a better solution for this.

我的问题是，在指定shop_id时，如何只从pivot表检索产品的价格?最后，我的目标如下所述，可能有更好的解决方案。

Explanation

Furthermore, why I need this is the following. I have a categories table as well. And in my index view I want to do something like this:

此外，我需要它的原因如下。我还有一个分类表。在我的索引视图中，我想做这样的事情:

@foreach($categories as $category) // All categories
    {{ $category->name }} // Display the name of the category

    @foreach($category->products as $product) // Loop through all related products for each category
        {{ $product->name }}
        {{ $product->price }}
    @endforeach

@endforeach

Now the trick is that the price is coming from the pivot table. I want to display the above based on the shop_id. Ideally, I just want to create a query where I select everything that I need, and then use that collection in my foreach, so I do not have to use specific methods in my views. So basically what I need is something along the lines of this:

现在的诀窍是价格来自于数据透视表。我想基于shop_id显示上面的内容。理想情况下，我只想创建一个查询，在其中选择我需要的所有内容，然后在foreach中使用该集合，这样我就不必在视图中使用特定的方法。所以基本上我需要的是这样的东西

select
    categories->with('products') // Select all categories while eager loading the products
    price // Collected from the pivot table where the shop_id is equal to the given shop_id

Database Tables

CREATE TABLE `categories` (
`id`,
  `user_id`,
  `name`,
  `created_at`,
  `updated_at`
)

CREATE TABLE `shops` (
`id`,
  `user_id`,
  `name`,
  `created_at`,
  `updated_at`
)

CREATE TABLE `products` (
`id`,
  `user_id`,
  `category_id`,
  `name`,
  `created_at`,
  `updated_at`
)

CREATE TABLE `product_shop` (
`id`,
  `product_id`,
  `shop_id`,
  `price`,
  `created_at`,
  `updated_at`
)

Ideal end result (with the price collected from the pivot table included):

4 个解决方案

#1

In your product.php (Model) file define this relation

在你的产品。php(模型)文件定义了这个关系。

public function priceCol($shopId)
{
  return $this->belongsTo(ProductShop::class,'product_id')->where('shop_id',$shopId);
}

For retrieving the price of a specific product

检索特定产品的价格

$product = Product::find(1);
$price = $product->priceCol($shopId)->price;

#2

Using eloquent:

使用的:

$shop=Shop::where('id',$id)->first();
$shop->pivot->price;

Also, make sure you use ->withPivot in relation:

另外，请确保使用->与pivot的关系:

public function products()
{
    return $this->belongsToMany('App\Product')->withPivot('price');;
}

#3

In your Product model define this

在你的产品模型中定义这个

public function shops () {
    return $this->belongsToMany(Shop::class)->wirhPivot('price'); 
}

Then you can

然后你就可以

Product::with('shops')->get();

To eager load and the resulting collection will have the price

对渴望的负载和产生的集合将有价格

#4

Figured it out myself in the end, since all the answers above were not 100% what I was looking for, unfortunately.

最后我自己算出来了，不幸的是，上面所有的答案都不是我想要的100%。

public function price($shop_id)
{
    return $this->shops()->where('shop_id', $shop_id)->first()->pivot->price;
}

#1