mysql - 更新对象时重复键输入

Question

我有一个 Product 对象，它有多个 Shop 对象，因为商店可以以不同的价格/条件提供相同的产品。

我有一个产品的编辑 View ，其中列出了该产品有售的商店。

当我对产品的商店进行调整时，例如。价格;我收到商店已存在于数据库中的错误消息。我知道该产品存在，但我需要更新数据。

SQLSTATE[23000]:违反完整性约束:1062 键“PRIMARY”的重复条目“1-1”

    public function update(Request $request, $slug)
    {
        $product = Product::with('shops', 'type')->where('slug', $slug)->first();
       [... snip ...]

        $i = 0;
        foreach($product->shops as $shop) {
            $shop = request('shop');
            $product->shops()->attach($product->id, [
              'shop_id' => $shop[$i]['id'],
              'price' => $shop[$i]['price'],
              'url' => $shop[$i]['url']
            ]);
            $i++;
        }

        $product->save();

      return redirect('/'.$slug)->with('success', 'Product has been updated');
    }

$product->update(); 产生相同的结果。

编辑:

Product.php

class Product extends Model
{

    protected $appends = ['lowest_price'];

    public function shops(){
        return $this->belongsToMany('App\Shop')->withPivot('price','url');
    }

    public function type(){
        return $this->belongsTo('App\Type');
    }

    public function getLowestPriceAttribute()
    {
        $lowest_price = NULL;
        foreach($this->shops as $shop) {
            if(is_null($lowest_price)) { 
                $lowest_price = (double)$shop->pivot->price;
            }

            if($lowest_price > (double)$shop->pivot->price) {
                $lowest_price = (double)$shop->pivot->price;
            }
        }
        return $lowest_price;
    }

}

Shop.php

class Shop extends Model
{
    //
}

店铺迁移

public function up()
    {
        Schema::create('shops', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('url');
            $table->string('logo');
            $table->timestamps();
        });

        [... snip ...]
    }

编辑2:

有关错误的更多信息:

 Illuminate \ Database \ QueryException (23000)
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-1' for key 'PRIMARY' (SQL: insert into `product_shop` (`price`, `product_id`, `shop_id`, `url`) values (500.00, 1, 1, http://test.com))

'CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `make` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `model` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `video` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `manufacturer_specs` text COLLATE utf8_unicode_ci NOT NULL,
  `top_speed` decimal(8,1) NOT NULL,
  `range` decimal(8,1) NOT NULL,
  `weight` decimal(8,1) NOT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `lowest_price` decimal(8,1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_slug_unique` (`slug`),
  KEY `products_type_id_index` (`type_id`),
  CONSTRAINT `products_type_id_foreign` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

'CREATE TABLE `product_shop` (
  `product_id` int(10) unsigned NOT NULL,
  `shop_id` int(10) unsigned NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `url` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`product_id`,`shop_id`),
  KEY `product_shop_product_id_index` (`product_id`),
  KEY `product_shop_shop_id_index` (`shop_id`),
  CONSTRAINT `product_shop_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_shop_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

'CREATE TABLE `shops` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

编辑3:如果我单击更新按钮，即使我没有更改任何内容，我也会收到错误消息

Answer 1

您正在尝试使用相同的键添加另一个产品到商店的关系，这就是您看到索引违规的原因。

除了使用attach，你还可以使用sync:

$product->shops()->sync(
    [
        $shop[$i]['id'] => [
            'price' => $shop[$i]['price'],
            'url' => $shop[$i]['url']
        ]
    ], false);

重要的部分是第二个参数，它禁用分离其他相关项。您还可以使用 syncWithoutDetaching。

详情见:

Docs

Api