Laravel中为company_balances表建立balance与amount的约束关系

你之前尝试给balance字段加外键的做法完全错误——外键是用来关联两个表的字段约束，和计算字段总和没有关系，这部分代码需要删掉。

下面提供两种可行方案，根据你的需求选择：

方案一：数据库触发器（MySQL/MariaDB适用） #

通过数据库触发器，在插入、更新、删除记录时自动维护balance的累计值（这里假设按vendor_id分组统计每个供应商的总额）。

修改你的迁移文件如下：

Schema::create('company_balances', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
    $table->float('balance');
    $table->float('amount');
    $table->text('particulars');
    $table->foreignId('vendor_id')->nullable()->constrained()
        ->onDelete('cascade')->onUpdate('cascade');
});

// 插入记录后更新当前供应商的balance总和
DB::statement('
    CREATE TRIGGER update_balance_after_insert
    AFTER INSERT ON company_balances
    FOR EACH ROW
    BEGIN
        UPDATE company_balances
        SET balance = (SELECT SUM(amount) FROM company_balances WHERE vendor_id = NEW.vendor_id)
        WHERE vendor_id = NEW.vendor_id;
    END
');

// 更新记录后重新计算当前供应商的balance总和
DB::statement('
    CREATE TRIGGER update_balance_after_update
    AFTER UPDATE ON company_balances
    FOR EACH ROW
    BEGIN
        UPDATE company_balances
        SET balance = (SELECT SUM(amount) FROM company_balances WHERE vendor_id = NEW.vendor_id)
        WHERE vendor_id = NEW.vendor_id;
    END
');

// 删除记录后重新计算当前供应商的balance总和
DB::statement('
    CREATE TRIGGER update_balance_after_delete
    AFTER DELETE ON company_balances
    FOR EACH ROW
    BEGIN
        UPDATE company_balances
        SET balance = (SELECT SUM(amount) FROM company_balances WHERE vendor_id = OLD.vendor_id)
        WHERE vendor_id = OLD.vendor_id;
    END
');

方案二：Laravel模型事件 #

完全在应用层面处理，不需要依赖数据库触发器，兼容性更好。

在CompanyBalance模型中添加事件逻辑：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class CompanyBalance extends Model
{
    protected $fillable = ['amount', 'balance', 'particulars', 'vendor_id'];

    protected static function booted()
    {
        // 插入新记录前，计算当前供应商的累计余额并赋值
        static::creating(function ($record) {
            $total = self::where('vendor_id', $record->vendor_id)->sum('amount') + $record->amount;
            $record->balance = $total;
        });

        // 更新记录后，重新计算并更新当前供应商所有记录的balance
        static::updated(function ($record) {
            $total = self::where('vendor_id', $record->vendor_id)->sum('amount');
            self::where('vendor_id', $record->vendor_id)->update(['balance' => $total]);
        });

        // 删除记录后，重新计算并更新当前供应商所有记录的balance
        static::deleted(function ($record) {
            $total = self::where('vendor_id', $record->vendor_id)->sum('amount');
            self::where('vendor_id', $record->vendor_id)->update(['balance' => $total]);
        });
    }
}

补充说明 #

如果你的需求是每条记录的balance是到该条记录为止的累计值（而非当前供应商的总余额），需要调整逻辑：按created_at排序，计算到当前记录的amount总和。这种情况下，触发器和模型事件的逻辑都需要修改排序条件，比如在求和时加上WHERE created_at <= NEW.created_at（触发器）或where('created_at', '<=', $record->created_at)（模型事件）。

内容的提问来源于stack exchange，提问作者Mahmud Abdullayev