[TIL] 10/8 좋아요 기능 구현, 서브쿼리-count(), EXISTS,AS

1. 좋아요 기능 구현

 

1.1. 좋아요 db 설계

 

likes 테이블 만들기

CREATE TABLE `book-shop`.`likes` (
  `user_id` INT NOT NULL,
  `liked_book_id` INT NOT NULL,
  PRIMARY KEY (`user_id`));

 

FK 설정

ALTER TABLE `book-shop`.`likes` 
ADD INDEX `liked_book_id_idx1` (`liked_book_id` ASC) VISIBLE;
;
ALTER TABLE `book-shop`.`likes` 
ADD CONSTRAINT `user_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `book-shop`.`users` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `liked_book_id`
  FOREIGN KEY (`liked_book_id`)
  REFERENCES `book-shop`.`books` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

 

 

1.2. 좋아요 추가 API

const addlike = (req, res) => {
    // 카테고리 전체 목록 리스트
    const {liked_book_id} = req.params;
    const {user_id} = req.body;

    let sql = `INSERT INTO likes (user_id, liked_book_id) VALUES (?, ?);`;
    let values = [user_id, liked_book_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);
    })
}

 

 

1.3. 좋아요 삭제 API

const removeLike = (req, res) => {
    // 좋아요 제거(취소)
    const {liked_book_id} = req.params;
    const {user_id} = req.body;

    let sql = `DELETE FROM likes WHERE user_id = ? AND liked_book_id = ?;`;
    let values = [user_id, liked_book_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);
        }
    )
}

 

 

2. 서브 쿼리

 

서브쿼리는 다른 SQL 쿼리 내에서 실행되는 쿼리로, 일반적으로 SELECT, INSERT, UPDATE, DELETE 쿼리 내에 포함된다. 

메인 쿼리의 일부로 실행되며, 결과를 반환해서 메인 쿼리에서 참조하거나, 조건으로 사용될 수 있다. (쉽게 말해 쿼리 안에 쿼리!)

 

2.1. count()

• 특정 조건에 맞는 행의 개수를 반환
• NULL 값을 제외하고 개수를 셈
• 주로 SELECT 문에서 사용되며, 그룹화된 데이터를 처리할 때 자주 사용

 

아래의 사진에서는 liked_book_id=1 이라는 조건에 해당하는 열이 3개이기 때문에 3을 돌려주는 것을 확인할 수 있다. 

SELECT count(*) FROM likes WHERE liked_book_id=1;

 

 

 

2.2. AS 

• SQL 에서 별칭(Alias)을 지정할 때 사용하는 키워드
• 열(row) 이나 테이블에 별칭을 부여할 수 있음

아래에서는 서브쿼리의 결과 열에 별칭을 부여하고 있다. 

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

 

 

2.3. EXISTS

• 서브쿼리의 결과가 존재하는 여부를 확인하는 데 사용
• 서브쿼리에서 하나 이상의 행이 반환되면 1을 반환, 반환된 행이 없으면 0을 반환

아래에서는 user_id=1 이고 liked_book_id=1 인 열의 존재 여부를 확인하고 있다.

SELECT EXISTS (SELECT * FROM likes WHERE user_id=1 AND liked_book_id=1);

 

 

3. 도서 API 에 좋아요 여부 추가

 

현재 db 테이블의 관계

 

likes 테이블은 유저가 좋아요 누른 책의 id 를 가지고 있다. 프론트에서 책에 대한 정보를 보여줄 때 책에 눌린 좋아요의 갯수(likes) 도 보여주고 싶은데 db 테이블에 likes 컬럼이 없다. 

 

따로 likes 컬럼을 만들어두고 likes 테이블에서 참조해오는 방법도 있겠지만, 화면에서만 보여주면 되니 컬럼을 만들지 않고 화면에 보여줄 때만 likes 컬럼을 붙여서 보여주려고 한다. 서브 쿼리를 이용해서 테이블에 컬럼을 추가해보자. 

 

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

 

sql 쿼리에 likes 서브 쿼리를 넣어주면 된다. 

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

 

전체 도서 조회 코드

const allBooks = (req, res) => {
    const {category_id, news, limit, currentPage} = req.query;

    let offset = limit * (currentPage-1);
    
    let sql = `SELECT *, (SELECT count(*) FROM likes WHERE liked_book_id=books.id) AS likes FROM books`;
    let values = [];

    if (category_id && news) {
        sql += ` WHERE category_id=? AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()`;
        values.push(category_id);
    }
    else if (category_id) {
        sql += ` WHERE category_id=?`;
        values.push(category_id);
    }
    if (news) {
        sql += ` WHERE pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()`;
    }

    sql += ` LIMIT ? OFFSET ?`
    values.push(parseInt(limit), offset);

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

 

 

3.2. 개별 도서 조회 시, 사용자가 도서에 좋아요를 했는지 여부 포함시키기

 

방법1) count(*) 사용하기

 

user_id=숫자 & liked_book_id=숫자 인 조건에 맞는 열의 수를 세어서 돌려주는 sql 쿼리문이다. 

사용자가 책에 좋아요를 누르지 않았다면 0 이 출력될 것이고, 좋아요를 눌렀다면 1이 출력될 것이므로 이 쿼리문으로 유저가 좋아요를 눌렀는지 여부를 알아낼 수 있다. 

SELECT count(*) FROM likes WHERE user_id=숫자 AND liked_book_id=숫자;

 

방법2) EXISTS 사용하기

 

user_id=숫자 & liked_book_id=숫자 인 조건에 맞는 열이 있는지 여부를 체크하는 sql 쿼리문이다. 

사용자가 책에 좋아요를 누르지 않았다면 거짓이므로 0이 출력될 것이고, 좋아요를 눌렀다면 참이므로 1이 출력될 것이므로 이 쿼리문으로 유저가 좋아요를 눌렀는지 여부를 알아낼 수 있다. 

SELECT EXISTS (SELECT * FROM likes WHERE user_id=1 AND liked_book_id=1);

 

 

현재 LEFT JOIN 으로 categor_name 을 함께 출력하고 있다. 이 쿼리문에 좋아요 눌렀는지 여부(liked)와 현재 책에 눌린 좋아요 수(likes)를 추가로 출력하려고 한다. 

SELECT *,
    (SELECT count(*) FROM likes WHERE liked_book_id=books.id) AS likes,
    (SELECT EXISTS (SELECT * FROM likes WHERE user_id=1 AND liked_book_id=1)) AS liked
FROM books 
LEFT JOIN category 
ON books.category_id = category.category_id
WHERE books.id=1;

 

 

길어보이지만 잘라서 분석을 해보자면 이해할 수 있다!

 

likes 컬럼 추가

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

 

likes 컬럼 추가

(SELECT EXISTS (SELECT * FROM likes WHERE user_id=1 AND liked_book_id=1)) AS liked

 

LEFT JOIN 으로 category_name 컬럼 추가

FROM books 
LEFT JOIN category 
ON books.category_id = category.category_id
WHERE books.id=1;

 

 

개별 도서 조회 전체 코드

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

    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();   
        }
    )
}