movie/app/Models/PostTag.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;
use App\Models\DTO\SearchData;

class PostTag extends Model
{
    protected $table = 'tb_post_tag';
    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 board()
    {
        return $this->belongsTo(Board::class);
    }

    public function post()
    {
        return $this->belongsTo(Post::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    /**
     * 게시글 태그 조회
     */
    public function data(SearchData $params): object
    {
        $query = $this->query();
        $query->from(function($q) use($params) {
            $q->select(
                'tb_board.name AS boardName',
                'tb_board.code',
                'tb_post_tag.*',
                'tb_post.subject',
                'tb_post.user_id',
                'users.sid',
                'users.name'
            );

            if($params->keyword) {
                switch ($params->field) {
                    case 'tb_post_tag.id' :
                    case 'users.id' :
                    case 'users.sid' :
                        $q->where($params->field, '=', $params->keyword);
                        break;
                    case 'tb_post.subject' :
                    case 'tb_post_tag.tag' :
                    case 'users.name' :
                    case 'users.email' :
                        $q->where($params->field, 'LIKE', "%{$params->keyword}%");
                        break;
                }
            }

            if($params->startDate || $params->endDate) {
                if($params->startDate) {
                    $q->where('tb_post_tag.created_at', '>=', $params->startDate . ' 00:00:00');
                }
                if($params->endDate) {
                    $q->where('tb_post_tag.created_at', '<=', $params->endDate . ' 23:59:59');
                }
            }

            if($params->boardID) {
                $q->where('tb_post_tag.board_id', '=', $params->boardID);
            }

            $q->from('tb_post_tag');
            $q->join('tb_post', 'tb_post.id', '=', 'tb_post_tag.post_id');
            $q->join('tb_board', 'tb_board.id', '=', 'tb_post_tag.board_id');
            $q->leftJoin('users', 'users.id', '=', 'tb_post.user_id');

        }, 'TT');

        $query->select(
            'TT.id',
            'TT.boardName',
            'TT.post_id',
            'TT.board_id',
            'TT.user_id',
            'TT.created_at',
            'TT.code',
            'TT.subject',
            'TT.sid',
            'TT.name',
            DB::raw('GROUP_CONCAT(DISTINCT TT.tag SEPARATOR ", ") AS tag')
        );

        $query->groupBy('TT.id', 'TT.post_id', 'TT.board_id', 'TT.user_id', 'TT.created_at', 'TT.code', 'TT.subject', 'TT.sid', 'TT.name');
        $list = $query->paginate($params->perPage, ['*'], 'page', $params->page);
        $total = $this->count();
        $rows = $list->count();

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

    /**
     * 게시글 태그 저장 (쉼표(,) 구분하여 값 삽입)
     */
    public function register(Post $post, ?string $tags): void
    {
        if ($tags) {
            foreach (explode(',', $tags) as $tag) {
                $tag = trim($tag);
                if ($tag) {
                    $this->insert([
                        'board_id' => $post->board_id,
                        'post_id' => $post->id,
                        'tag' => $tag,
                        'ip_address' => IP_ADDRESS,
                        'user_agent' => USER_AGENT,
                        'created_at' => now()
                    ]);
                }
            }

            $post->updateTagRows($post->id);
        }
    }

    /**
     * 게시글 태그 저장 (쉼표(,) 구분하여 값 삽입)
     */
    public function updater(Post $post, ?string $tags): void
    {
        // 기존 태그 삭제
        $this->remove($post);

        // 새로 등록
        $this->register($post, $tags);
    }

    /**
     * 게시글 태그 삭제
     */
    public function remove(Post $post): void
    {
        $this->where([
            ['board_id', $post->board_id],
            ['post_id', $post->id]
        ])->delete();
    }

    /**
     * 태그 목록 조회
     */
    public function getAllTags(): Collection
    {
        $sql = "
            SELECT
                PSTG.tag AS name,
                COUNT(DISTINCT PSTG.post_id) AS count
            FROM tb_post_tag PSTG
            INNER JOIN tb_post PST
                ON PST.id = PSTG.post_id
            WHERE PST.is_delete = 0
            GROUP BY PSTG.tag
            ORDER BY PSTG.tag ASC;
        ";

        return collect(DB::select($sql));
    }

    /**
     * 태그별 게시글 조회
     */
    public function getPostsByTag(SearchData $params): object
    {
        $sql = "
            SELECT COUNT(DISTINCT PST.id) AS total FROM tb_post PST JOIN tb_post_tag PSTG ON PST.id = PSTG.post_id WHERE PST.is_delete = 0 AND PSTG.tag = ?;
        ";

        $total = DB::selectOne($sql, [$params->name])->total;

        $sql = "
             SELECT
                PST.id,
                PST.board_id,
                PST.user_id,
                PST.thumbnail,
                PST.subject,
                PST.content,
                PST.created_at,
                BRD.name AS boardName,
                BRD.code AS boardCode,
                USR.sid AS userSID,
                USR.name AS userName,
                GROUP_CONCAT(DISTINCT PSTG_all.tag ORDER BY PSTG_all.tag SEPARATOR ', ') AS tags
            FROM tb_post PST
            INNER JOIN tb_post_tag PSTG ON PSTG.post_id = PST.id
            INNER JOIN tb_board BRD ON BRD.id = PST.board_id
            LEFT JOIN users USR ON USR.id = PST.user_id
            LEFT JOIN tb_post_tag PSTG_all ON PSTG_all.post_id = PST.id
            WHERE PST.is_delete = 0 AND PSTG.tag = ?
            GROUP BY
                PST.id, PST.board_id, PST.user_id, PST.subject, PST.created_at, BRD.name, BRD.code, USR.sid, USR.name
            ORDER BY
                PST.created_at DESC, PST.id DESC
            LIMIT ? OFFSET ?;
        ";

        $list = DB::select($sql, [
            $params->name,
            $params->perPage,
            $params->offset
        ]);

        $rows = count($list);

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