movie/app/Models/Exp.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Exp extends Model
{
    protected $table = 'tb_exp';
    protected $primaryKey = 'id';

    public $keyType = 'int';
    public $incrementing = true;
    public $timestamps = true;

    const CREATED_AT = 'created_at';
    const UPDATED_AT = null;

    protected $guarded = [];

    /**
     * 경험치를 추가합니다.
     */
    public function register(array $params): bool
    {
        // 이미 등록된 내역이라면 건너뜀
        if($this->where([
            ['user_id', $params['user_id']],
            ['type', $params['type']],
            ['related_id', $params['related_id']],
            ['method', $params['method']],
        ])->exists()) {
            return false;
        }

        if(!$this->insert($params)) {
            return false;
        }

        return $this->updateUserExp($params['user_id']);
    }

    /**
     * 회원 경험치를 삭제합니다.
     */
    public function remove(array $params)
    {
        $this->where([
            ['user_id', $params['user_id']],
            ['type', $params['type']],
            ['related_id', $params['related_id']],
            ['method', $params['method']],
        ])->delete();

        return $this->updateUserExp($params['user_id']);
    }

    /**
     * 회원 경험치 갱신
     */
    public function updateUserExp(int $userID)
    {
        return (new User)->where('id', $userID)->update([
            'exp' => $this->getUserExpSum($userID)
        ]);
    }

    /**
     * 회원 경험치 합계
     */
    public function getUserExpSum(int $userID): int
    {
        $sql = "SELECT COALESCE(SUM(`value`), 0) AS total FROM tb_exp WHERE user_id = ?;";

        return DB::selectOne($sql, [$userID])->total;
    }
}