movie/app/Models/Visit.php

<?php

namespace App\Models;

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

class Visit extends Model
{
    protected $table = 'visits';
    protected $primaryKey = 'id';

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

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

    protected $guarded = [];

    /**
     * 방문자 수 등록
     */
    public function register(Request $request): ?\Shetabit\Visitor\Models\Visit
    {
        return visitor()->visit($request->user());
    }

    /**
     * 오늘 방문자 수
     */
    public function todayCount(): int
    {
        return $this->whereDate('created_at', now()->today())->distinct()->count('ip');
    }

    /**
     * 어제 방문자 수
     */
    public function yesterdayCount(): int
    {
        return $this->whereDate('created_at', now()->yesterday())->distinct()->count('ip');
    }

    /**
     * 누적 방문자 수
     */
    public function totalCount(): int
    {
        $sql = "
            SELECT SUM(`CNT`) AS `total` FROM (
                SELECT COUNT(DISTINCT `ip`) AS CNT FROM `visits` WHERE 1 GROUP BY DATE_FORMAT(created_at, '%Y%m%d')
            ) D;
        ";

        return DB::selectOne($sql)->total;
    }
}