🎯 서브 쿼리를 통해 테이블에 컬럼을 추가합니다.

📙 Today I Learned

데이터베이스 설계

categories TABLE 수정

books 테이블 SELECT 시에 칼럼으로 넘겨줄 때 id 이름이 중복되어 category_id로 이름을 변경해주었습니다.

category_id integer [primary key]
category_name varchar

books TABLE 수정

likes는 likes 테이블에서 가져와 칼럼으로 추가할 것이기 때문에 삭제하였습니다.

Table books {
  id integer [primary key]
  title varchar
  img integer
  category_id integer
  form varchar
  isbn varchar
  summary text
  detail text
  author varchar
  pages integer
  contents text
  price integer
  pub_date date
}

---

SQL

칼럼 개수 반환

조건에 해당하는 행의 개수를 반환합니다.

SELECT COUNT(*) FROM 테이블명 WHERE 칼럼1이름 = 칼럼2이름

특정 조건 만족 여부 확인

조건에 만족하는 행이 존재하면 1 아니면 0을 반환합니다.

SELECT EXISTS (SELECT 1 FROM 테이블명 WHERE 칼럼1이름 = ? AND 칼럼2이름 = ?)

🤔 왜 SELECT 1을 쓰는걸까?

EXISTS 에서는 존재 여부만을 판단하기 때문에 *을 통해 모든 칼럼을 가져오는 것은 비효율적이기 때문입니다.

서브 쿼리 별칭으로 불러오기

서브 쿼리는 다른 쿼리 내부에서 실행되는 SQL 쿼리를 의미합니다.

별칭이 새로운 칼럼으로 추가되어 테이블을 반환하게 됩니다.

SELECT *, (SELECT count(*) FROM 테이블명 WHERE 칼럼1이름 = 칼럼2이름0) AS 별칭 FROM 테이블명 

---

도서 API 설계

👉 수정한 부분은 주황색 글씨로 나타냅니다.

전체 도서 조회

• Method : GET

• URL :/books?limit=개수&currentPage=페이지번호

• HTTP Status Code : 200 Ok

• Request Body : x

• Response Body :

[
  {
    "id" : 도서 id,
    "title": "도서 제목",
    "img" : 이미지 id,
    "summary" : "요약 설명",
    "author" : "도서 작가",
    "price" : 가격,
    "likes" : 좋아요 수,
    "pub_date" : "출간 날짜"
  },
  {
    "id" : 도서 id,
    "title": "도서 제목",
    "img" : 이미지 id,
    "summary" : "요약 설명",
    "author" : "도서 작가",
    "price" : 가격,
    "likes" : 좋아요 수,
    "pub_date" : "출간 날짜"
  },
  ...
]

개별 도서 조회

• Method : GET

• URL : /books/:id

• HTTP Status Code : 200 Ok

• Request Body

{
  "email": "사용자가 입력한 이메일",
  "password" : "사용자가 입력한 비밀번호"
}

• Response Body :

{
  "id" : 도서 id,
  "title": "도서 제목",
  "img" : 이미지 id,
  "category_name" : "카테고리",
  "form" : "포맷",
  "isbn" : "isbn",  
  "summary" : "요약 설명",
  "detail" : "자세한 설명",
  "author" : "도서 작가",
  "pages" : 쪽 수,
  "contents" : "목차",
  "price" : 가격,
  "likes" : 좋아요 수,
  "liked" : boolean,
  "pub_date" : "출간 날짜"
}

카테고리별 신간 도서 조회

• Method : GET

• URL : /books?categoryId=:categoryId&news={boolean}

• HTTP Status Code : 200 Ok

• Request Body : x

• Response Body :

[
  {
    "id" : 도서 id,
    "title": "도서 제목",
    "img" : 이미지 id,
    "summary" : "요약 설명",
    "author" : "도서 작가",
    "price" : 가격,
    "likes" : 좋아요 수,
    "pub_date" : "출간 날짜"
  },
  {
    "id" : 도서 id,
    "title": "도서 제목",
    "img" : 이미지 id,
    "summary" : "요약 설명",
    "author" : "도서 작가",
    "price" : 가격,
    "likes" : 좋아요 수,
    "pub_date" : "출간 날짜"
  },
  ...
]

• 참고사항 : news가 true일 때 신간 조회 기준은 출간일로부터 1달 이내

---

좋아요 API 설계

좋아요 추가

• Method : POST

• URL : /likes/:bookId

• HTTP Status Code : 200 Ok

• Request Body : token > header "authorization"

• Response Body : x

• TODO : 추후 api 수정 , 현재는 토큰으로 하지 않고,userId를 body값으로 받아서 처리
  

좋아요 취소

• Method : DELETE

• URL : /likes/:bookId

• HTTP Status Code : 200 Ok

• Request Body : token > header "authorization"

• Response Body : x
  

• TODO : 추후 api 수정 , 현재는 토큰으로 하지 않고,userId를 body값으로 받아서 처리

---

LikeController.js

const conn = require('../mariadb');
const { StatusCodes } = require('http-status-codes');

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

  const sql = 'INSERT INTO likes (user_id, liked_book_id) VALUES (?, ?)';
  const values = [userId, likedBookId];
  conn.query(sql, values, (err, result) => {
    if (err) {
      console.log(err);
      return res.status(StatusCodes.BAD_REQUEST).end();
    }

    return res.status(StatusCodes.CREATED).json(result);
  });
};

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

  const sql = 'DELETE FROM likes WHERE user_id =? AND liked_book_id = ?';
  const values = [userId, likedBookId];
  conn.query(sql, values, (err, result) => {
    if (err) {
      console.log(err);
      return res.status(StatusCodes.BAD_REQUEST).end();
    }

    return res.status(StatusCodes.OK).json(result);
  });
};

module.exports = {
  addLike,
  removeLike,
};

• addLike : likes 테이블에 user_id 와 liked_book_id를 저장합니다.

• removeLike : likes 테이블에서 user_id 와 liked_book_id를 삭제합니다.
  

---

BookController.js

const conn = require('../mariadb');
const { StatusCodes } = require('http-status-codes');

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

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

  if (categoryId && news) {
    sql +=
      ' WHERE category_id = ? AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()';
    values.push(categoryId);
  } else if (categoryId) {
    sql += ' WHERE category_id = ?';
    values.push(categoryId);
  } else 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, result) => {
    if (err) {
      console.log(err);
      return res.status(StatusCodes.BAD_REQUEST).end();
    }
    if (result.length) {
      return res.status(StatusCodes.OK).json(result);
    } else {
      return res.status(StatusCodes.NOT_FOUND).end();
    }
  });
};

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

  const sql = `SELECT *, 
  	(SELECT count(*) FROM likes WHERE liked_book_id = books.id) AS likes, 
  	(SELECT EXISTS (SELECT 1 FROM likes WHERE user_id = ? AND liked_book_id = ?)) AS liked 
  	FROM books 
  	LEFT JOIN categories 
  	ON books.category_id = categories.category_id 
  	WHERE books.id = ?`;
  const values = [userId, id, id];
  conn.query(sql, values, (err, result) => {
    if (err) {
      console.log(err);
      return res.status(StatusCodes.BAD_REQUEST).end();
    }

    if (result[0]) {
      return res.status(StatusCodes.OK).json(result);
    } else {
      return res.status(StatusCodes.NOT_FOUND).end();
    }
  });
};

module.exports = {
  allBooks,
  bookDetail,
};

• allBooks : books 테이블에서 모든 컬럼을 가져오면서, likes 테이블에서 해당 책의 좋아요 개수(likes)를 조회합니다.

• bookDetail : books 테이블에서 모든 칼럼을 가져오면서, likes 테이블에서 해당 책의 좋아요 개수와 유저가 해당 책을 좋아요를 했는지(liked) 여부를 조회합니다.

---

✏️ 한 줄 회고

테이블을 조인하지 않아도 서브 쿼리와 칼럼 별칭으로 원하는 데이터를 효율적으로 가져올 수 있어 편리했습니다.