movie/app/Models/Movie/Movie.php

<?php

namespace App\Models\Movie;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use App\Models\DTO\SearchData;
use App\Http\Traits\PagingTrait;
use App\Http\Traits\CommonTrait;

class Movie extends Model
{
    use CommonTrait;
    use PagingTrait;

    protected $table = 'tb_movie';
    protected $primaryKey = 'id';

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

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

    protected $guarded = [];

    public function list(SearchData $params): object
    {
        $where = $this->buildWhereQuery($params);

        $sql = sprintf("
            SELECT
                COUNT(*) AS total
            FROM tb_movie MVI
            WHERE TRUE %s;
        ", $where);

        $total = DB::selectOne($sql)->total;

        $sql = sprintf("
            SELECT
                MVI.*,
                MVD.main_img,
                (SELECT ROUND(AVG(`rate`), 1) AS rate FROM tb_movie_review WHERE movie_cd = MVI.movie_cd) AS rate
            FROM tb_movie MVI
                LEFT JOIN tb_movie_detail MVD ON MVI.movie_cd = MVD.movie_cd
            WHERE TRUE %s
            ORDER BY
                MVI.open_dt DESC
            LIMIT ?, ?;
        ", $where);

        $list = $this->getPaginator(
            DB::select($sql, [$params->offset, $params->perPage]),
            $total, $params->perPage, $params->page, $params->path
        );

        $rows = $list->count();

        return (object)[
            'total' => $total,
            'rows' => $rows,
            'list' => $list
        ];
    }

    private function buildWhereQuery(SearchData $params): string
    {
        $where = "";

        if($params->keyword) {
            $where .= sprintf(" AND (MVI.movie_nm LIKE '%%%s%%' OR MVI.movie_nm_en LIKE '%%%s%%') ", $params->keyword, $params->keyword);
        }

        if($params->director) {
            $where .= sprintf(" AND JSON_UNQUOTE(JSON_EXTRACT(directors, JSON_UNQUOTE(JSON_SEARCH(directors, 'one', '%s')))) = '%s'", $params->director, $params->director);
        }

        if($params->type && is_array($params->type)) {
            $where .= sprintf(" AND type_nm IN(%s)", $this->setArraySingQuote($params->type));
        }

        if($params->genre && is_array($params->genre)) {
            $where .= sprintf(" AND rep_genre_nm IN(%s)", $this->setArraySingQuote($params->genre));
        }

        if($params->sYear || $params->eYear) {
            if($params->sYear) {
                $where .= sprintf(" AND prdt_year >= %d", $params->sYear);
            }
            if($params->eYear) {
                $where .= sprintf(" AND prdt_year <= %d", $params->eYear);
            }
        }

        if($params->sOpenDt || $params->eOpenDt) {
            if($params->sOpenDt) {
                $where .= sprintf(" AND open_dt >= %d", $params->sOpenDt);
            }
            if($params->eOpenDt) {
                $where .= sprintf(" AND open_dt <= %d", $params->eOpenDt);
            }
        }

        return $where;
    }

    public function isExists(string $movieCd): int
    {
        $sql = "
            SELECT IF(COUNT(*) > 0, 1, 0) AS isExists FROM tb_movie MVI JOIN tb_movie_info MVIO WHERE MVI.movie_cd = ?;
        ";

        return DB::selectOne($sql, [$movieCd])->isExists;
    }
}