좋아요 DB 설계하기

좋아요 추가 구현

const addLike = (req, res) => {
    const { id } = req.params;
    const { user_id } = req.body;

    let sql = `INSERT INTO likes (user_id, liked_book_id) VALUES (?,?)`;
    let values = [user_id, id];

    conn.query(sql, values,
        (err, results) => {
            if (err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end();
            }
            return res.status(StatusCodes.OK).json(results);
        }
    )
};

좋아요 삭제 구현 (취소)

const removeLike = (req, res) => {
    const { id } = req.params;
    const { user_id } = req.body;

    let sql = `DELETE FROM likes WHERE user_id = ? AND liked_book_id = ?`;
    let values = [user_id, id];

    conn.query(sql, values,
        (err, results) => {
            if (err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end();
            }
            return res.status(StatusCodes.OK).json(results);
        }
    )
}

좋아요 개수 세서 books 테이블에 컬럼 추가해서 출력하기

SELECT *,
(SELECT count(*) FROM likes WHERE liked_book_id = books.id) AS likes FROM books;

도서 테이블 전체 조회 + 컬럼 1개 추가하기

SELECT *,
			값 AS 새로 추가할 컬럼명
			FROM books;
			
SELECT *,
			(각 행마다 likes 테이블에 liked_book_id로 가지고 있는 행 수) AS likes
			FROM books;
			
SELECT *,
			(SELECT count(*) FROM likes WHERE books.id = liked_book_id) AS likes
			FROM books;

개별 도서 조회 시, 사용자가 좋아요를 헀는지 여부를 포함하기

SELECT EXIST (SELECT * FROM likes WHERE user_id = 1 AND liked_book_id = 3)

const bookDetail = (req, res) => {
    let  book_id = req.params.id;
    let { user_id } = req.body;
    let sql = `SELECT *,
                (SELECT count(*) FROM likes WHERE liked_book_id = books.id) AS likes,
                (SELECT EXISTS (SELECT * FROM likes WHERE user_id = ? AND liked_book_id =?)) AS liked
                FROM books
                LEFT JOIN category
                ON books.category_id = category.category_id
                WHERE books.id =?`;
    let values = [user_id, book_id, book_id];
    conn.query(sql, values,
        (err, results)=> {
            if (err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end();
            }
            if (results[0])
                return res.status(StatusCodes.OK).json(results[0]);
            else
                return res.status(StatusCodes.NOT_FOUND).end();
        }
    )
};