Laravel中带WHERE子查询的复杂INSERT语句实现方案问询

我明白你想实现的核心需求：只有当用户还没有持有某个商品时（也就是该商品的买入和卖出交易抵消后净持有量为0），才允许插入新的购买交易。原生SQL用了INSERT...SELECT的原子操作来避免竞态问题，下面我帮你把这个逻辑转换成Laravel的Query Builder和Eloquent写法，两种方式各有适用场景：

一、Query Builder 写法（贴近原生SQL，原子操作） #

这种方式直接对应你的原生SQL，利用insertUsing方法实现INSERT...SELECT，是原子性的，不会出现高并发下的竞态问题（比如两个请求同时检测到净持有量为0然后都插入）。

简洁版 #

use Illuminate\Support\Facades\DB;

$userId = 1;
$itemId = 186808;

DB::table('transactions')
    ->insertUsing(
        ['user_id', 'item_id', 'type', 'created_at', 'updated_at'],
        DB::table('transactions')
            ->selectRaw('?, ?, ?, NOW(), NOW()', [$userId, $itemId, 'bought'])
            ->whereRaw(
                '(SELECT SUM(CASE WHEN type = ? THEN 1 WHEN type = ? THEN -1 END) FROM transactions WHERE user_id = ? AND item_id = ?) = 0',
                ['bought', 'sold', $userId, $itemId]
            )
    );

更易读的结构化版本 #

把净持有量的子查询单独抽出来，代码更清晰：

use Illuminate\Support\Facades\DB;

$userId = 1;
$itemId = 186808;

// 构建计算净持有量的子查询
$netHoldingSubquery = DB::table('transactions')
    ->where('user_id', $userId)
    ->where('item_id', $itemId)
    ->selectRaw('SUM(CASE WHEN type = "bought" THEN 1 WHEN type = "sold" THEN -1 END) as net');

// 执行带条件的插入
DB::table('transactions')
    ->insertUsing(
        ['user_id', 'item_id', 'type', 'created_at', 'updated_at'],
        DB::table('transactions')
            ->select(
                DB::raw("$userId as user_id"),
                DB::raw("$itemId as item_id"),
                DB::raw('"bought" as type'),
                DB::raw('NOW() as created_at'),
                DB::raw('NOW() as updated_at')
            )
            ->whereExists(function ($query) use ($netHoldingSubquery) {
                $query->select(DB::raw(1))
                    ->fromSub($netHoldingSubquery, 'sub')
                    ->where('sub.net', 0);
            })
    );

二、Eloquent 写法（面向对象风格） #

如果你习惯用Eloquent模型操作，可以先查询净持有量，再判断是否插入。不过要注意，这种“先查后插”的方式在高并发场景下可能有竞态问题，需要配合事务和锁来保证原子性。

基础版（适合低并发场景） #

use App\Models\Transaction;

$userId = 1;
$itemId = 186808;

// 计算用户对该商品的净持有量，默认0（如果没有任何交易记录）
$netHolding = Transaction::where('user_id', $userId)
    ->where('item_id', $itemId)
    ->selectRaw('SUM(CASE WHEN type = "bought" THEN 1 WHEN type = "sold" THEN -1 END) as net')
    ->value('net') ?? 0;

// 只有净持有量为0时才插入购买记录
if ($netHolding === 0) {
    Transaction::create([
        'user_id' => $userId,
        'item_id' => $itemId,
        'type' => 'bought',
        // 可以不用手动设置created_at和updated_at，只要模型开启了时间戳（默认开启）
    ]);
}

高并发安全版（带事务和锁） #

用lockForUpdate锁住查询的交易记录，防止其他事务在这段时间内修改数据，保证整个操作的原子性：

use App\Models\Transaction;
use Illuminate\Support\Facades\DB;

$userId = 1;
$itemId = 186808;

DB::transaction(function () use ($userId, $itemId) {
    // 锁住该用户该商品的所有交易记录，防止并发修改
    $netHolding = Transaction::where('user_id', $userId)
        ->where('item_id', $itemId)
        ->lockForUpdate()
        ->selectRaw('SUM(CASE WHEN type = "bought" THEN 1 WHEN type = "sold" THEN -1 END) as net')
        ->value('net') ?? 0;

    if ($netHolding === 0) {
        Transaction::create([
            'user_id' => $userId,
            'item_id' => $itemId,
            'type' => 'bought',
        ]);
    }
});

两种方式对比 #

• Query Builder的insertUsing：完全对应原生SQL的原子操作，性能好，无竞态问题，适合高并发场景。
• Eloquent写法：更符合Laravel的面向对象风格，代码可读性高，但需要额外处理并发问题，适合低并发或者需要在插入前后做更多业务逻辑的场景。

内容的提问来源于stack exchange，提问作者Sakibul Alam