[7주차 - DAY5] 도서 API 구현(2)

 

 

DB

외래키 제약 조건

books 테이블의 category_id는 category 테이블의 기본키인 id를 참조한다. DB에 외래키 제약 조건을 추가해 보자.

제약 조건의 이름은 category_id로 설정했다.

 

 

SQL

카테고리 조인

도서 목록을 응답으로 보내줄 때 카테고리 아이디가 아니라 카테고리 이름이 들어가야 한다. 따라서 books 테이블과 category 테이블을 카테고리 아이디를 기준으로 조인해줘야 한다.

SELECT *
FROM books
LEFT JOIN category
ON books.category_id = category.id;

 

신간 목록 조회

DATE_SUB(기준 날짜, INTERVAL) 함수를 이용하여 특정 시간에서 지정된 값만큼 뺄 수 있다. 참고로 더하는 함수는 DATE_ADD이다.

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

 

페이징

도서 목록을 보여줄 때 사용자가 지정한 개수만큼만 한 번에 보내줘야 한다. 사용자가 더보기나 다음 페이지와 같은 버튼을 클릭하면 다시 지정된 개수만큼 보내준다. 이것을 페이징이라고 한다.

MySQL에서는 LIMIT과 OFFSET을 이용해 페이징을 구현할 수 있다.

LIMIT은 출력할 행의 개수, OFFSET은 시작 행의 번호를 의미한다. 행은 0번부터 시작이라는 것을 유의해야 한다.

SELECT * FROM books LIMIT 3 OFFSET 0;

 

 

도서 API 구현

카테고리와 신간 여부에 따른 도서 목록 조회

쿼리로 조회할 카테고리 아이디와 신간을 조회할 것인지, 한 페이지에 몇 개의 도서를 출력할 것인지와 현재 페이지를 받는다.

books 테이블의 모든 컬럼을 출력하는 sql문은 공통되므로 앞으로 빼내준다.

카테고리 아이디와 신간 여부가 쿼리로 들어왔다면 해당 sql문을 추가해 주고, 몇 개의 도서를 출력할 것인가와 현재 페이지를 이용해 LIMIT과 OFFSET을 설정해 준다.

const allBooks = (req, res) => {
    let { category_id, news, limit, currentPage } = req.query;
    let offset = limit * (currentPage - 1);

    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 = [parseInt(category_id)];
    } else if (category_id) {
        sql += `WHERE category_id = ?`;
        values = [parseInt(category_id)];
    } 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, 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();
            }
        });
};

 

개별 도서 조회

도서의 상세 페이지에서는 카테고리도 표시해줘야 하기 때문에 books 테이블과 category 테이블을 조인해서 카테고리 이름이 무엇인지 알 수 있도록 해준다.

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

    const 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.length) {
                return res.status(StatusCodes.OK).json(results[0]);
            } else {
                return res.status(StatusCodes.NOT_FOUND).end();
            }
        })
};