多个字段累加 & 更新多个字段

inc

$saveRes = Member::where('id', $memberId)
                    ->inc('money', $changeAmount)
                    ->inc('point', $changeNum)
                    ->data(['status' => 1, 'update_time' => time()])
                    ->update();

原生

$sql = 'UPDATE `cd_member`
SET `point`=`point` + `add_point`,
    `total_point`=`total_point` + `add_point`,
    `week_point`=`week_point` + `add_point`,
    `add_point`=0
WHERE id = ' . $memberId;
$res = Db::execute($sql);

update

$updateData = [
    'total_point' => Db::raw('total_point+add_point'),
    'week_point'  => Db::raw('week_point+add_point'),
    'add_point'    => Constants::DEFAULT_VALUE,
];
$res = Member::update($updateData, ['id' => $memberId]);

两字段比较

方式一

[2021-04-30T11:30:53+08:00][sql] UPDATE `cd_task`  SET `last_task_count` = IFNULL(last_task_count, 0) + 1  WHERE  `id` = 1026  AND ( `last_task_count` <= `total` ) [ RunTime:0.045582s ]

$res = Task::where('id', $taskId)
            ->whereColumn('last_task_count', '<=', 'total')
            ->exp('last_task_count', 'IFNULL(last_task_count, 0) + 1')
            ->update();

方式二

$where = [
    ['id', '=', $taskId],
    ['last_task_count', 'exp', Db::raw('<=`total`')],
];
$res   = Task::where($where)
    ->exp('last_task_count', 'IFNULL(last_task_count, 0) + 1')
    ->update();

方式三

$res   = Task::whereColumn([
	['title', '=', 'name'],
    ['last_task_count', '<=', 'total'],
])
    ->exp('last_task_count', 'IFNULL(last_task_count, 0) + 1')
    ->update();

Where 条件中使用FIND_IN_SET函数

!FIND_IN_SET

$fId = 4;  // 需要过滤的ID
$where[] = ['status', '=', Constants::STATUS_ENABLE];
$where[] = ['', 'exp', Db::raw("!FIND_IN_SET({$fId},f_ids)")];

$memberList = Member::withoutField('id', name')
                ->where($where)
                ->order('order_id ASC')
                ->select()
                ->toArray();

对应SQL语句

[2021-06-25T14:21:25+08:00][sql] SELECT `id`,`name` FROM `cd_member` WHERE  `status` = 1  AND ( !FIND_IN_SET(4,f_ids) ) ORDER BY `order_id` ASC [ RunTime:0.045002s ]