[TIL] 10/7 API 구현, LEFT JOIN, DATE_ADD, DATE_SUB, 페이징

이전 내용

[TIL] 10/4 도서 데이터베이스, API 구현

 

 

1. JOIN

 

1.1. books - category 테이블 연관관계 설정하기

 

 

books 테이블의 category_id 컬럼은 category 테이블의 id 컬럼을 참조하는 FK -> 데이터베이스에서 연관관계를 설정해줘야 함

 

설정하고 나면 왼쪽 마름모 색이 빨간색으로 바뀐 것을 확인할 수 있다. 

 

 

1.2. LEFT JOIN 

 

books 테이블과 category 테이블을 LEFT JOIN 시키고 SELECT 해주면, category_name 을 함께 출력할 수 있다. 

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

 

개별 도서 조회 API에서 sql 문을 JOIN 하고 SELECT 해서 category_name 도 함께 출력하도록 수정

// bookController.js
// 개별 도서 조회
const bookDetail = (req, res) => {
    const {id} = req.params;
    console.log(id);
    let sql = `SELECT * FROM books LEFT JOIN category 
                ON books.category_id = category.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();   
        }
    )
}

 

 

 

2. SQL 시간 범위 구하기 (DATE_ADD, SUB)

 

2.1. 시간 더하기 - DATE_ADD(기준 날짜, INTERVAL)

 

기준날짜를 넣고, INTERVAL 에는 기준 날짜로부터 간격을 벌려줄 값을 넣어주면 된다. 

 

SQL 은 한 달을 n일을 한달로 잡고 기준값에 n 일을 더해서 돌려주는 것이 아니라 정말 한 달 단위로 값을 돌려준다. 

무슨 말이냐 하면, 기준일이 1월 31일로 INTERVAL 을 1달로 잡고 돌려보면 2월 29일을 돌려준다.

(1월31일~2월29일은 날 수로 치면 1단이 안되지만 한 달 단위로 돌려주기 때문에 돌려주는 것)

 

만약 현재를 기준 값으로 넣고 싶다면 기준 값을 NOW() 로 써주면 된다. 

 

 

2.2. 시간 빼기 - DATE_SUB(기준 날짜, INTERVAL)

 

시간 더하기와 사용법이 똑같다. ADD 를 SUB 로만 고쳐주면 된다. 

 

 

2.3. 시간 범위 설정해서 SELECT 하기

 

1달 이내에 출간된 책을 신간이라고 했을 때, 신간을 골라내기 위해 한 달 이내 값들을 SELECT 해보자.

BETWEEN 을 사용해서 지금으로부터 1달 전부터 지금까지의 기간을 필터링해서 신간을 걸러낼 수 있다. 

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

 

 

 

3. 도서 API 구현

 

3.1. 카테고리별 / 신간 / 도서 목록 조회

 

쿼리로 category_id 와 news(신간만 볼 것인지)를 받아서 아래와 같이 분류

• category_id && news -> 카테고리별 신간 조회
• category_id -> 카테고리별 조회
• news -> 전체에서 신간 조회
• 둘 다 X -> 전체 목록 조회

const allBooks = (req, res) => {
    const {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;
    }
    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) 
                res.status(StatusCodes.OK).json({results});
            else
                res.status(StatusCodes.NOT_FOUND).end();
        }
    )   
}

 

 

3.2. 페이징 구현하기

 

도서가 엄청 많은 경우 한번에 모든 정보를 다 보여줄 수 없기 때문에 페이징을 해주어야 한다. 

 

페이징(paging)

-> 많은 양의 데이터를 한 번에 모두 불러오지 않고, 일정한 단위로 나누어 필요한 데이터만 부분적으로 불러오는 기술/방법

 

 

데이터베이스에 페이징해보기

 

방법1)

• LIMIT : 출력할 행의 수
• OFFSET : 시작 지점 (= 지금 몇페이지인지)

SELECT * FROM books LIMIT 숫자 OFFSET 숫자;

 

방법2)

SELECT * FROM books LIMIT 시작위치, 출력할 행 수;

 

 

도서 API 의 URI

/books?limit={page당 도서 수}&currentPage={현재 page}

 

LIMIT, OFFSET 값은 필요한 값을 프론트엔드에서 쿼리에 담아서 보내준다.

OFFSET 값은 프론트에서 굳이 계산할 필요가 없으므로 페이지를 넘길 때 현재 페이지 번호를 담아서 보내주면 된다. (OFFSET 값은 백엔드에서 알아서 계산할 수 있음 -> limit * currentPage-1)

 

const allBooks = (req, res) => {
    const {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.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();
        }
    )   
}