2024-02-14(45일차) - Spring

민짱·2024년 2월 14일

[산대특] 프롬프트AI & OpenAPI & 공공데이터를 활용한 웹앱개발자 양성과정

목록 보기

45/85

📅2024. 02.14 45일차

🎬2024_01_Spring_AM

좋아요 기능 구현

띵킹타임

일단 추천?? DB 테이블을 만들어야 할 것 같은데??

 # reactionPoint(추천) 테이블 생성
    CREATE TABLE reactionPoint (
        id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        regDate DATETIME NOT NULL,
        updateDate DATETIME NOT NULL,
        memberId INT(10) UNSIGNED NOT NULL,
        relTypeCode CHAR(50) NOT NULL COMMENT '관련 데이터 타입 코드',
        relId INT(10) UNSIGNED NOT NULL COMMENT '관련 데이터  번호',
        `point` SMALLINT(2) NOT NULL
    );

테스트 데이터 설정

# reactionPoint 테스트 데이터 생성
# 1번 회원이 1번 글에 싫어요
INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
relTypeCode = 'article',
relId = 1,
`point` = -1;

# 1번 회원이 2번 글에 좋아요
INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
relTypeCode = 'article',
relId = 2,
`point` = 1;

# 2번 회원이 1번 글에 싫어요
INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 1,
`point` = -1;

# 2번 회원이 2번 글에 싫어요
INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 2,
`point` = -1;

# 3번 회원이 1번 글에 좋아요
INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 3,
relTypeCode = 'article',
relId = 1,
`point` = 1;

쿼리 수정

@Select("""
				SELECT A.*, M.nickname AS extra__writer,
				IFNULL(SUM(RP.point),0) AS extra__sumReactionPoint,
				IFNULL(SUM(IF(RP.point > 0, RP.point, 0)),0) AS extra__goodReactionPoint,
				IFNULL(SUM(IF(RP.point < 0, RP.point, 0)),0) AS extra__badReactionPoint
				FROM article AS A
				INNER JOIN `member` AS M
				ON A.memberId = M.id
				LEFT JOIN reactionPoint AS RP
				ON A.id = RP.relId AND RP.relTypeCode = 'article'
				WHERE A.id = #{id}
				GROUP BY A.id
				""")
	public Article getForPrintArticle(int id);

script 열 때는 꺽새 > 아니면 <로 해야함.

좋아요 싫어요 수정

리스트에 좋아요 싫어요 합 표시
list 쿼리문 수정

@Select("""
			<script>
			SELECT A.*, M.nickname AS extra__writer,
			IFNULL(SUM(RP.point),0) AS extra__sumReactionPoint,
			IFNULL(SUM(IF(RP.point &gt; 0, RP.point, 0)),0) AS extra__goodReactionPoint,
			IFNULL(SUM(IF(RP.point &lt; 0, RP.point, 0)),0) AS extra__badReactionPoint
			FROM article AS A
			INNER JOIN `member` AS M
			ON A.memberId = M.id
			LEFT JOIN reactionPoint AS RP
			ON A.id = RP.relId AND RP.relTypeCode = 'article'	
			WHERE 1
			<if test="boardId != 0">
				AND A.boardId = #{boardId}
			</if>
			<if test="searchKeyword != ''">
				<choose>
					<when test="searchKeywordTypeCode == 'title'">
						AND A.title LIKE CONCAT('%',#{searchKeyword},'%')
					</when>
					<when test="searchKeywordTypeCode == 'body'">
						AND A.body LIKE CONCAT('%',#{searchKeyword},'%')
					</when>
					<otherwise>
						AND A.title LIKE CONCAT('%',#{searchKeyword},'%')
						OR A.body LIKE CONCAT('%',#{searchKeyword},'%')
					</otherwise>
				</choose>
			</if>
			GROUP BY A.id
			ORDER BY A.id DESC
			<if test="limitFrom >= 0 ">
				LIMIT #{limitFrom}, #{limitTake}
			</if>
			</script>
			""")
	public List<Article> getForPrintArticles(int boardId, int limitFrom, int limitTake, String searchKeywordTypeCode,
			String searchKeyword);