如何用Laravel Query Builder或MySQL维护多对多关联数据表的增改

一、MySQL 原生语句实现 #

1. 创建记录并建立关联 #

（1）新增采购记录并关联已有价格

-- 插入采购记录，获取自增ID
INSERT INTO PURCHASE (`Country`, `Medicine`, `Quantity`, `Purchase date`)
VALUES ('加拿大（Canada）', 'Aspirin', 15, '2024-05-20 10:00:00');

SET @purchase_id = 1131182;

-- 关联价格表中ID为1的记录
INSERT INTO PRICE_PURCHASE (price_id, purchase_id)
VALUES (1, @purchase_id);

（2）新增价格记录并关联已有采购

-- 插入价格记录，获取自增ID
INSERT INTO PRICES (`Country`, `Year`, `Medicine`, `Price`)
VALUES ('加拿大（Canada）', 2024, 'Aspirin', 20);

SET @price_id = 1131182;

-- 关联采购表中ID为0的记录
INSERT INTO PRICE_PURCHASE (price_id, purchase_id)
VALUES (@price_id, 0);

（3）同时创建采购、价格记录并关联

-- 创建采购记录
INSERT INTO PURCHASE (`Country`, `Medicine`, `Quantity`, `Purchase date`)
VALUES ('美国（USA）', 'VitaminD', 8, '2024-05-20 11:00:00');
SET @purchase_id = 1131182;

-- 创建价格记录
INSERT INTO PRICES (`Country`, `Year`, `Medicine`, `Price`)
VALUES ('美国（USA）', 2024, 'VitaminD', 12);
SET @price_id = 1131182;

-- 建立关联
INSERT INTO PRICE_PURCHASE (price_id, purchase_id)
VALUES (@price_id, @purchase_id);

2. 更新记录与维护关联 #

（1）更新采购记录（关联关系保留）

UPDATE PURCHASE
SET Quantity = 20, `Purchase date` = '2024-05-21 09:00:00'
WHERE id = 0;

（2）更新价格记录（关联关系保留）

UPDATE PRICES
SET Price = 19, Year = 2023
WHERE id = 1;

（3）替换采购记录的关联价格

-- 删除旧关联
DELETE FROM PRICE_PURCHASE WHERE purchase_id = 0;
-- 新增新关联
INSERT INTO PRICE_PURCHASE (price_id, purchase_id)
VALUES (4, 0);

二、Laravel Query Builder 实现 #

先定义模型关联 #

Purchase 模型

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Purchase extends Model
{
    protected $table = 'PURCHASE';
    protected $fillable = ['Country', 'Medicine', 'Quantity', 'Purchase date'];

    public function prices()
    {
        return $this->belongsToMany(Price::class, 'PRICE_PURCHASE', 'purchase_id', 'price_id');
    }
}

Price 模型

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Price extends Model
{
    protected $table = 'PRICES';
    protected $fillable = ['Country', 'Year', 'Medicine', 'Price'];

    public function purchases()
    {
        return $this->belongsToMany(Purchase::class, 'PRICE_PURCHASE', 'price_id', 'purchase_id');
    }
}

1. 创建记录并关联 #

（1）新增采购并关联已有价格

// Query Builder 方式
$purchaseId = \DB::table('PURCHASE')->insertGetId([
    'Country' => '加拿大（Canada）',
    'Medicine' => 'Aspirin',
    'Quantity' => 15,
    'Purchase date' => now()
]);
\DB::table('PRICE_PURCHASE')->insert([
    'price_id' => 1,
    'purchase_id' => $purchaseId
]);

// 模型关联方式更简洁
$purchase = Purchase::create([
    'Country' => '加拿大（Canada）',
    'Medicine' => 'Aspirin',
    'Quantity' => 15,
    'Purchase date' => now()
]);
$purchase->prices()->attach(1);

（2）新增价格并关联已有采购

// Query Builder 方式
$priceId = \DB::table('PRICES')->insertGetId([
    'Country' => '加拿大（Canada）',
    'Year' => 2024,
    'Medicine' => 'Aspirin',
    'Price' => 20
]);
\DB::table('PRICE_PURCHASE')->insert([
    'price_id' => $priceId,
    'purchase_id' => 0
]);

// 模型关联方式
$price = Price::create([
    'Country' => '加拿大（Canada）',
    'Year' => 2024,
    'Medicine' => 'Aspirin',
    'Price' => 20
]);
$price->purchases()->attach(0);

（3）同时创建采购、价格并关联

// 模型方式
$purchase = Purchase::create([
    'Country' => '美国（USA）',
    'Medicine' => 'VitaminD',
    'Quantity' => 8,
    'Purchase date' => now()
]);
$price = Price::create([
    'Country' => '美国（USA）',
    'Year' => 2024,
    'Medicine' => 'VitaminD',
    'Price' => 12
]);
$purchase->prices()->attach($price->id);

2. 更新记录与维护关联 #

（1）更新采购记录

// Query Builder 方式
\DB::table('PURCHASE')
    ->where('id', 0)
    ->update([
        'Quantity' => 20,
        'Purchase date' => now()
    ]);

// 模型方式
Purchase::find(0)->update([
    'Quantity' => 20,
    'Purchase date' => now()
]);

（2）更新价格记录

// Query Builder 方式
\DB::table('PRICES')
    ->where('id', 1)
    ->update([
        'Price' => 19,
        'Year' => 2023
    ]);

// 模型方式
Price::find(1)->update([
    'Price' => 19,
    'Year' => 2023
]);

（3）维护关联关系

// 替换采购的所有关联价格（自动删除旧关联，新增新关联）
Purchase::find(0)->prices()->sync([4]);

// 新增关联（保留原有关联）
Purchase::find(0)->prices()->attach(5);

// 移除单个关联
Purchase::find(0)->prices()->detach(1);

内容的提问来源于stack exchange，提问作者jose-cuevas