[데브코스] 도서 목록 조회 페이징 적용 API

join으로 카테고리 아이디-이름 매핑

 

1. fk 추가 후

2. join 쿼리 실행

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

 

3. api에서 join 쿼리 실행

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

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

  if (category_id) {
    let sql = `SELECT * FROM books LEFT JOIN category ON books.category_id = category.id WHERE books.category_id=?`;
    conn.query(sql, category_id, (err, result) => {
      if (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();
    });
  } else {
    let sql = `SELECT * FROM books LEFT JOIN category ON books.category_id = category.id`;
    conn.query(sql, (err, result) => {
      if (err) {
        return res.status(StatusCodes.BAD_REQUEST).end();
      }

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

const getBookById = (req, res) => {
  //parseInt 적용 시, id 속성을 구조 분해 시도
  //parseInt는 숫자를 반환하므로, 구조 분해 불가. '
  //parseInt는 문자열을 정수로 변환하는데, 객체인 req.parmas 전체를 전달하는 오류가 있었다.

  let { id } = req.params;

  let sql = `SELECT * FROM books LEFT JOIN category ON books.category_id = category.id WHERE books.id=?`;

  conn.query(sql, id, (err, result) => {
    if (err) {
      return res.status(StatusCodes.BAD_REQUEST).end();
    }

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

module.exports = {
  getAllBooks,
  getBookById,
};

 

 

기간 조건 추가하기

데이터이스 시간 범위를 구하기 위해서는 DATE_ADD()와 DATE_SUB()를 사용한다

- DATE_ADD(기준날짜, INTERVAL)

- DATE_SUB(기준날짜, INTERVAL)

 

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

시간 범위 지정, 범위 지정은 BETWEEN을 사용한다.

 

SELECT * FROM books WHERE pub_date BETWEEN  DATE_SUB(NOW(), INTERVAL 10 DAY) AND NOW();

 

기존의 복잡하던 if-else 문을 sql 문 조건문 처리로 분리하여 구조 개선

//변경 전
  if (category_id) {
    let sql = `SELECT * FROM books LEFT JOIN category ON books.category_id = category.id WHERE books.category_id=?`;
    conn.query(sql, category_id, (err, result) => {
      if (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();
    });
  } else {
    let sql = `SELECT * FROM books LEFT JOIN category ON books.category_id = category.id`;
    conn.query(sql, (err, result) => {
      if (err) {
        return res.status(StatusCodes.BAD_REQUEST).end();
      }

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

 //변경 후
 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, result) => {
    if (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();
    }
  });

 

이제 쿼리가 추가될 때마다 conn.query를 호출하여 처리하지 않고, sql 문 조건 문내에서 SQL 쿼리에 대한 분기만 처리한 후, conn.query에서 위에서 처리한 sql과 values를 받아 처리할 수 있다. 

 

조건문 작성 시, 조건의 순서를 항상 유의해야 함!

 

오류: 개별조회 시 id가 무조건 0으로 나오는 문제

원인: book 테이블의 id와, category의 id가 join 시 충돌하여 발생하는 문제였음

 

기존에 아래처럼 쿼리를 사용중이었음

  let sql = `SELECT * FROM books LEFT JOIN category ON books.category_id = category.id WHERE books.id=?`;

SELECT *를 사용하여 books와 category 테이블 모두 id 컬럼을 가져, JOIN 결과에서도 두 id가 충돌 한 ㄱ것. mariadb에서는 나중에 오는 테이블로 id 값이 덮어써지거나, 일부 경우에는 기본값인 0으로 설정될 수 잇다.

 

확인 결과, 무조건 0 이 아닌 category의 id 값으로 출력되고 있었다.

 

외래키 관계를 이용해, 필요한 category_name만 가져오도록 수정했다.

  let sql = `SELECT books.*, (SELECT category_name FROM category WHERE id = books.category_id) as category_name 
             FROM books 
             WHERE books.id=?`;

JOIN 대신 서브쿼리를 사용하여  category_name 만 가져오도록 변경

books 테이블 모든 컬럼은 유지하고, category 테이블과의 불필요한 JOIN을 제거하였다

이제 books 테이블의 데이터는 온전히 유지되면서(id 포함), category_name만 추가로 가져와 정상적으로 id가 출력되는 것을 확인할 수 잇따.

 

페이징 적용

쿼리문으로 처리

SELECT * FROM books LIMIT 4 OFFSET 8

- LIMIT 출력할 행의 수
- OFFSET: 시작 지점

 

고정하지 않고, 동적으로 받아 처리하도록

-limit은 url로 받아서 처리

 

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

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

  let offset = (currentPage - 1) * limit;

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

 

오류: ER_PARSE_ERROR(1064)

sql 구문 오류가 발생

 

1. 

```

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? WHERE category_id=? AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) ...' at line 1",
  sql: 'SELECT * FROM books LIMIT 4 OFFSET ? WHERE category_id=? AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()'
}

```

 

2. 

```

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0'' at line 1",
  sql: "SELECT * FROM books  WHERE category_id=4 AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() LIMIT 0 OFFSET '0'"
}

```

 

3. 

```

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''true' OFFSET 4' at line 1",
  sql: "SELECT * FROM books  WHERE category_id='0' AND pub_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() LIMIT 'true' OFFSET 4"
}

```

 

원인: 

1. sql 문법의 순서. limit과 offset을 모든 쿼리에 적용하기 위해 where 절 앞에 배치하였는데, 올바른 순서가 아니어서 발생한 문제였다.

sql 문의 올바른 순서: 

절 (Clause) 설명

SELECT	조회할 컬럼 지정
FROM	테이블 지정
WHERE	조건 지정 (필터링)
GROUP BY	그룹화 (집계용)
HAVING	그룹 필터링
ORDER BY	정렬
LIMIT / OFFSET	행 개수 제한, 시작 위치 지정

따라서 SELECT * FROM 위치에서 limit, offset을 설정하면 ㅁ안되고, 조건 문 종료 후 최후에 sql += 하여 limit, offset 조건을 추가해주어야 한다.

2. offset 값이 문자열 0으로 전달되었다.

숫자 타입이어야 하는데, 문자열이 사용되어 발생한 오류. parseInt로 숫자 변환

 

3. 파라미터 바인딩 순서 문제

values 배열의 값이 sql의 ? 위치와 순서대로 매칭되어야 한다.

where 절의 조건 파라미터가 먼저 오고, limit.offset이 나중에 와야 함

 

해결
```

let sql = "SELECT * FROM books";  // 기본 쿼리
if (조건) {
  sql += " WHERE ...";           // WHERE 절 추가
}
sql += " LIMIT ? OFFSET ?";      // 마지막에 LIMIT/OFFSET 추가

```

 

```

let values = [];
// WHERE 절 파라미터 먼저 추가
if (category_id) values.push(parseInt(category_id));
// LIMIT/OFFSET 파라미터는 마지막에 추가
values.push(parseInt(limit), parseInt(offset));

```

 

news 조건 처리로 변경

api 설계 방식 중, 파라미터 전달 방식과 조건 처리 방식이 있다. 기존에는 news를 파라미터 전달 방식으로 처리하고 있었는데, 파라미터 바인딩에서 문제가 발생했다.

```

-- 발생한 오류 쿼리
SELECT * FROM books WHERE category_id='0' AND pub_date BETWEEN ... LIMIT 'true' OFFSET 4

```

 

문제점:

- news 값이 where 절이 아닌 limit 절에 바인딩되어 limit true와 같은 잘못된 쿼리 생성

- values 배열에 news 값을 사용하면서 파라미터 순서가 꼬임

- 불필요한 파라미터 바인딩으로 sql 오류 발생

 

 

```

// 변경 전: 파라미터 바인딩 방식
if (news) {
  sql += " WHERE pub_date BETWEEN ... AND NOW()";
  values.push(news);  // 문제 발생
}

// 변경 후: 조건 처리 방식
if (news === "true") {
  sql += " WHERE pub_date BETWEEN ... AND NOW()";
  // 파라미터 바인딩 없이 조건만 처리
}

```

 

- 안정성: sql 인젝션 위험 제거, 파라미터 바인딩 오류 방지, 잘못된 입력 값 차단

- 명확성:최신 도서 여부라는 의도가 코드에 명확히 드러남- true/false로 단순화된 처리

- 효율성: 불필요한 파라미터 바인딩 제거, sql 쿼리 실행 성능 향상

 

지금은 true, false로 신간 도서 여부만 판별하여 가능한 구조. 만약 기간별 조회 등의 구조 개선 시, 아래 방향으로 확장 가능

```

//기간별 조회

// /books?timeRange=1m|3m|6m
const timeRangeMap = {
  '1m': 1,
  '3m': 3,
  '6m': 6
};

if (timeRange && timeRangeMap[timeRange]) {
  sql += " WHERE pub_date BETWEEN DATE_SUB(NOW(), INTERVAL ? MONTH) AND NOW()";
  values.push(timeRangeMap[timeRange]);
}

```

```

//커스텀 날짜 조회

// /books?dateFrom=2024-01-01&dateTo=2024-03-31
if (dateFrom && dateTo) {
  sql += " WHERE pub_date BETWEEN ? AND ?";
  values.push(dateFrom, dateTo);
}

```

 

강사님은 너무 오류 없이 잘 해보여서 한 시간동안 고민하다가 질문글까지 남겼는데 바로 다음 영상에 있어서 개허망하고 부끄러웟당. 두명 봤던데,,,. 역시 강의는 제때 잘 들어야 함