1021 SQL 시간 범위 구하기(신간 안내), 도서 목록 페이지 구현

 

<개별 도서 상세 조회 API 조인 구현>

books - category 연관관계 설정

• foreign key 연결
• 개별 도서 조회 시 조인한 sql문으로 변경

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

// 개별 도서 상세 조회
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 = ?`;

 

<(카테고리별, 신간 여부) 전체 도서 목록 조회 구현>

 

SQL 시간 범위 구하기

• 신간 : 한달이내 도서만 나오게 하기
• 시간 더하기 : DATE_ADD(기준일자, INTERVAL 시간 간격)
  

  SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

   
• 시간 빼기 : DATE_SUB(기준일자, INTERVAL 시간 간격)
  

  SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

   
• 시간 범위를 설정해서 SELECT문 구현
  • 최근 한달 이내를 신간으로 정의한다.

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

 

카테고리별, 신간, 도서 목록 조회

• 카테고리 아이디, 신간 여부가 쿼리스트링으로 오는지 여부에 따라 sql문 where절만 분기 처리
• 이때, if문의 순서가 중요

// (카테고리별, 신간 여부) 전체 도서 목록 조회
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];
    } 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()`;
    }
 
    conn.query(sql, values,
        (err, results) => {
            if(err){
                console.log(err)
                return res.status(StatusCodes.BAD_REQUEST).end()
            }

 

도서 목록 조회 페이징 구현하기

 

데이터베이스 페이징(Paging)

• 페이징 : 한 페이지당 몇개씩 보여줄지 결정하는 것
               sql문으로 처리 가능하다.
• LIMIT {출력할 행의 갯수} OFFSET {시작 지점}
  
  • LIMIT : 출력할 행의 갯수
  • OFFSET : 시작 지점(=내가 있는 페이지)
    

    SELECT * FROM books LIMIT 4 OFFSET 8;

     

 

• LIMIT {시작 지점, 행의 수}

SELECT * FROM books LIMIT 8, 4;

 

OFFSET 계산

• 현재 머무는 페이지를 currentPage로 상정해 요청 쿼리로 받고, offset을 따로 계산

let {category_id, news, limit, currentPage } = req.query;

    // limit : page당 도서 수   ex. 3
    // currentPage : 현재 몇 페이지 ex. 1, 2, 3 ...
    // offset : limit*(currentPage-1)  ex. 0, 3, 6, 9, 12 ...

    let offset = limit * (currentPage - 1);

 

신간 안내 페이징 구현

• 주의할 점 : 조건이 여러 개일 경우, limit문은 where절 뒤에 붙어야 한다.
• limit에 int값이 들어오도록 parseInt 해준다.

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

// limit문은 where절 뒤에 붙어야 한다.
sql += ` LIMIT ? OFFSET ?`;
values = [...values, parseInt(limit), offset];

// SELECT 쿼리문
conn.query(sql, values,
    (err, results) => {
        if(err){

 

---

SQL에서 시간 범위를 구하는 DATE_ADD()와 DATE_SUB()를 배워 유용했다. LIMIT OFFSET 문법도 앞으로 페이징할 때 많이 사용할 수 있을 것 같다.