2024년 10월 7일

✏️ books-category 연관 관계 설정

• JOIN을 하기 위해서는 FK를 먼저 설정하고 join 해야함!

  • category의 PK가 books테이블의 FK가 됨

  • books 테이블 설정

    

    

    

• 그리고 JOIN 시켜보자
• SELECT * FROM books LEFT JOIN category ON books.category_id = category.id;

그런데, category테이블의 name을 가져오면 book의 name인지, cateogry의 name인지 알 수 없음 -> AS 이용해서 별칭 지어주기

• SELECT name AS category_name FROM Bookshop.category;

그렇다고 해서 테이블의 필드가 바뀌는 것은 아님 : 오해no. just 별칭
그런데, 굳이 별칭을 사용해서 다른 곳에서 사용할 필요는 없음 → * 을 쿼리에서 쓰지 못함.
그럼 컬럼 하나하나 다 말해줘야함 → 테이블의 칼럼 이름을 그냥 바꾸는게 나음

• db 다이어그램 업데이트
  

✏️ 개별 도서 목록 조회 API(feat.category_name)

• 개별 도서 조회 -> 카테고리명 보여줘야함
• id값이 1인 친구만 보여주기 쿼리(카테고리 이름을 보여주기 위해 위에서 살펴본 JOIN 이용) : SELECT * FROM books LEFT JOIN category ON books.category_id = category.id WHERE id=1;

// BookController.js

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

  let sql = `SELECT * FROM books LEFT JOIN category
  ON books.category_id = category.id WHERE books.id = ?`;
  conn.query(sql, id, (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();
    }
  });
};

POSTMAN_ GET+localhost:9999/1

✏️ SQL 시간 범위 구하기(DATE_ADD, SUB)

• 시간 범위에 따라서 신간 도서 보여주기

• 데이터 더 넣어주고
  

• 데이터베이스 시간 범위 구하기

  • 시간 더하기 : DATE_ADD(기준 날짜, INTERVAL)
  • 시간 빼기 : DATE_SUB(기준 날짜, INTERVAL)
    *지금 날짜: NOW()

• 시간 범위를 설정해서 출간날짜 SELECT 하기

  • 최근 한달 이내 : 신간으로 범위 설정해서 선택

  • SELECT * FROM books WHERE pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW();

    

✏️ 카테고리별+신간 도서 목록 조회

• 카테고리별 신간 도서 목록 조회 (출간 한달 이내 조회)
  • url로 new를 쓰려했는데 예약어이므로 오류가 뜸 → news 로 받을 것임
  • 조건이 두개 일 때는 (WHERE 안에 두 개의 조건 쓸 때) AND로 묶어서 써준다
    • SELECT * FROM books WHERE category_id = 0 AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW();
      

// BookController.js

const conn = require("../mariadb"); // db 모듈
const { StatusCodes } = require("http-status-codes"); // status code 모듈

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

  let sql = "SELECT * 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 = [category_id, news];
  } else if (category_id) {
    sql += " WHERE category_id = ?";
    values = category_id;
  } else if (news) {
    sql +=
      " WHERE pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()";
    values = news;
  }

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

    if (results.length) {
      return res.status(StatusCodes.OK).json(results);
    } else {
      return res.status(StatusCodes.NOT_FOUND).end();
    }
  });
};

POSTMAN) GET + localhost:9999/books?category_id=0

POSTMAN) GET + localhost:9999/books?category_id=0&news=true

✏️ 도서 목록 조회 페이징 구현

: 선택한 개수 만큼 도서를 뿌려주고 다음 페이지로 넘어가도록 하기

💡 데이터 베이스 페이징(paging)

• 페이징 : 몇개씩 보여줄까?
  ex) SELECT * FROM books; → 전체 도서 리스트 100개
  … 8개씩 필요하면 ?
  … 4개씩 필요하면 ?
  → 한페이지에 데이터를 뿌려 줄 수 있는 만큼만 전달 하겠다는 의미

• 워크벤치에서 실습 먼저 해보자

  • SELECT * FROM books LIMIT (숫자) OFFSET (숫자)

    • LIMIT : 몇 개씩 주겠다 (3개씩 주겠다) → 출력할 행의 수
    • OFFSET : 시작할 위치(처음은 0부터 시작) → 시작 지점(=내가 .. 지금 몇페이지더라?)
      ex) SELECT * FROM books LIMIT 3 OFFSET 0; (1번째 페이지 3개 보겠다)
      ex) SELECT * FROM books LIMIT 3 OFFSET 3; (2번째 페이지 3개 보겠다)
      ex) SELECT FROM books LIMIT 5 OFFSET 5; (2번째 페이지 5개 보겠다)
      
      ex) SELECT FROM books LIMIT 4 OFFSET 8; (3번째 페이지 4개를 보겠다)
      

  • LIMIT과 OFFSET을 더 간단하게 쓸 수 있음 → 순서가 바뀜

    • SELECT * FROM books LIMIT 8, 4; (위와 같은 결과임)

      

💡 도서 목록 조회 페이징 구현

• (카테고리별, 신간 여부) 전체 도서 목록 조회 페이징을 구현해보자
  • SQL문을 이용해서 위에서 배운 것들을 뒤에 붙여주면 될 것 같은데..
    • 그러나, 우리가 LIMIT 과 OFFSET의 값을 지정하지 않음
      • 프론트엔드에서 원하는 LIMIT과 OFFSET 보내줘야함 ⇒ LIMIT, OFFSET은 프론트엔드가 전달해줄 것임
      • limit(갯수), offset(시작지점) : req url에 담아보냄
        • 그런데 프론트엔드가 굳이 offset을 계산해서 보내주는 것은 비효율적임 → 백엔드에서 계산해서 넣어주면됨
        • 따라서, 프론트엔드는 현재 페이지에서 몇개를 넣느냐만 요청해주면 됨 (지금 원하는 페이지)
        • /books?limit={page당 도서수}¤tPage={현재 page}
• 코드를 작성해보자

// BookController.js

const conn = require("../mariadb"); // db 모듈
const { StatusCodes } = require("http-status-codes"); // status code 모듈

// (카테고리별, 신간 여부) 전체 도서 목록 조회
const allBooks = (req, res) => {
  let { category_id, news, limit, currentPage } = req.query;

  // limit: page당 도서 수 ex. 3
  // currentPage: 현재 몇 페이지 ex. 1, 2, 3 ...
  // offset: 첫페이지니깐 0부터 시작하고, 두번째 페이지는 3부터 시작, 세번째 페이지는, 6 -> 0, 3, 6, 9, 12...
  // offset = limit * (currentPage-1)

  let offset = limit * (currentPage - 1);

  let sql = "SELECT * FROM books LIMIT ? OFFSET ?";
  let values = [limit, offset];
  if (category_id && news) {
    sql +=
      " WHERE category_id = ? AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()";
    values = [...values, category_id, news];
  } else if (category_id) {
    sql += " WHERE category_id = ?";
    values = [...values, category_id];
  } else if (news) {
    sql +=
      " WHERE pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()";
    values = [...values, news];
  }

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

    if (results.length) {
      return res.status(StatusCodes.OK).json(results);
    } else {
      return res.status(StatusCodes.NOT_FOUND).end();
    }
  });
};

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=1

400 에러 발생

콘솔창 에러를 보면,
숫자가 문자열로 들어가 있어서임 ⇒ let values= [parseInt(limit), offset]; 으로 고쳐야함 & offset은 이미 계산을 해줘서 숫자로 변환됨

let offset = limit * (currentPage - 1);

  let sql = "SELECT * FROM books LIMIT ? OFFSET ?";
  let values = [parseInt(limit), offset];

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=1

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=2

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=0&news=true

신간만 보려고 위처럼 url을 입력했더니 에러가 나왔다.

• 에러를 잡아보자

  • 코드를 봤을때 sql의 순서와 다른 것들도 봐야함

  • 일단, value에는 news가 들어갈 필요 없기 때문에 다 빼준다 → news는 true값으로 sql문에 영향력을 끼칠 일이 없음

  • LIMIT과 OFFSET이 sql문에서 앞으로 오면 에러가 나기 때문에 바꿔 줘야함 ⇒ WHERE 절이 앞에 있어야함

    • 순서가 잘 맞게 들어갔을 때

      

    • 순서가 잘못 들어갔을 때

      

// BookController.js

const conn = require("../mariadb"); // db 모듈
const { StatusCodes } = require("http-status-codes"); // status code 모듈

// (카테고리별, 신간 여부) 전체 도서 목록 조회
const allBooks = (req, res) => {
  let { category_id, news, limit, currentPage } = req.query;

  let offset = limit * (currentPage - 1);

  let sql = "SELECT * FROM books";
  let values = []; // sql문을 보면 category_id가 먼저 들어와야하니깐
									 // limit과 offset을 나중에 적어준다
  if (category_id && news) {
    sql +=
      " WHERE category_id = ? AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()";
    values = [category_id];
  } else if (category_id) {
    sql += " WHERE category_id = ?";
    values = [category_id];
  } else if (news) {
    sql +=
      " WHERE pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()";
  }

  sql += " LIMIT ? OFFSET ?"; // 나중에 적어주기
  values = [...values, parseInt(limit), offset]; // 배열에 추가하기

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

    if (results.length) {
      return res.status(StatusCodes.OK).json(results);
    } else {
      return res.status(StatusCodes.NOT_FOUND).end();
    }
  });
};

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=1

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=2

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=1&news=true

POSTMAN) GET + localhost:9999/books?limit=4¤tPage=1&category_id=0

🍏🍎 오늘의 느낀점 : 오늘은 도서 목록 조회에 대한 전반적인 내용을 배웠다. JOIN 쿼리를 통해 두 테이블을 합쳐서 데이터를 보여주고, 페이징을 백엔드에서 구현하는 방법을 배웠다. 예전에 일할때 페이징 작업에 대해서 어떻게 이루어지는건지 궁금했었는데 이런 방식으로 프론트와 주고 받으면서 페이징을 구현하는거구나 하는 것을 알았다. 연습 필요!!