[BookShop 프로젝트] 주문 API - LAST_INSERT_ID(), MAX()로 최신 데이터 가져오기, insertId, 벌크 INSERT

구현하고 있는 API -> https://ilove-ya.tistory.com/144

 

[table]

delivery (1)
↓
orders (N) (delivery_id FK)
↓
orderedBook (N) (order_id FK)

 

 

• delivery INSERT → 생성된 delivery_id 필요
• orders INSERT → 생성된 order_id 필요
• orderedBook INSERT → order_id 필요

주문 API 에서 특이한 점은 이전 INSERT 결과의 PK를 다음 쿼리에서 사용하여야한다는 점입니다. 

 

---

이를 위해서

직전에 insert한 데이터 PK 가져오는 방법

을 사용하여보겠습니다.

• LAST_INSERT_ID()
  : 이전 값을 들고오는 오류가 날 수 있음
  
  
• MAX() 
  : 최신값 -> id값이 가장 큰 값을 고르면 됨

SELECT max(id) FROM Bookshop.orderedBook;

SELECT last_insert_id();

 

// 주문하기
// 배송 정보 입력
INSERT INTO delivery (address, receiver, contact) VALUES ("용인시 수지구", "김가영", "010-1234-5678")
const delivery_id = SELECT max(id) FROM delivery

// 주문 정보 입력
INSERT INTO orders (book_title, total_quantity, total_price, user_id, delivery_id)
VALUES ("어린완자들", 3, 60000, 1, delivery_id);
const order_id = SELECT max(id) FROM orders;

// 주문 상세 목록 입력 
INSERT INTO orderedBook(order_id, book_id, quantity)
VALUES (order_id,1,1);
INSERT INTO orderedBook(order_id, book_id, quantity)
VALUES (order_id,3,2);

SELECT max(id) FROM Bookshop.orderedBook;
SELECT last_insert_id();

이렇게 하면 가장 최신의 배송정보를 주문정보에 넣어주고,

가장 최신의 주문 정보를 주문상세 목록에 넣어줍니다.

 

 

---

 

insertId

그런데 현재  코드에서

Node에서 conn.query() 실행했을 때 콜백의 results 값(INSERT 쿼리 실행 결과 메타데이터)를 살펴 보면, "insertId"가 있는 걸 알 수 있습니다. 

 

insertId : 방금 INSERT된 행의 PK 값을 의미

->

INSERT INTO delivery ... 를 실행했고

AUTO_INCREMENT가 3이었다는 뜻입니다. 

 

이를 활용하면 max(id)을 사용하지 않아도 됩니다. 

---

주문을 진행할 때 하나의 주문에 여러 개의 책을 한 번에 구매할 수 있습니다. 

이를 위해서는 주문이 진행되는 여러개의 책을 for문을 돌려 insert해줄 수 있지만, 이는 성능저하를 초래할 수 있습니다. 

INSERT INTO orderedBook ...
INSERT INTO orderedBook ...
INSERT INTO orderedBook ...

 

따라서 이를 하나의 쿼리로 처리시켜주는 방법을 사용해 보겠습니다. 

벌크 INSERT

INSERT INTO orderedBook(order_id, book_id, quantity)
VALUES 
(1, 10, 2),
(1, 15, 1),
(1, 21, 3);

이렇게 INSERT문의 VALUES 뒤에 ( ) 가 여러 개 붙으면 INSERT을 여러번 수행해 줄 수 있다는 겁니다. 

 

VALUES ?

이를 위해 이 방식으로 sql문으로 넣어두고 ?에 이중 배열를 넣어주면, 

MySQL Node driver가 자동으로 

[
  [1, 10, 2],
  [1, 15, 1],
  [1, 21, 3]
]

이중배열인 이 형태를

(1,10,2),
(1,15,1),
(1,21,3)

이러한 형태로 변형시켜줍니다. 

 

    sql = `INSERT INTO orderedBook(order_id, book_id, quantity)
            VALUES ?;`;

    //items.. 배열 : 요소들을 하나씩 꺼내서 (foreach문 돌려서)
    values = [];
    items.forEach((items) => {
        values.push([order_id, items.book_id, items.quantity]);
    })
    conn.query(sql, [values], 
        (err, results) => {
            if(err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end(); 
            }

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

 

 

코드에 적용을 해보자면,  forEach을 돌려서 이중배열형태로 책들의 정보를 전체 배열의 하나의 요소의 배열로 push해주었습니다 .

 

그리하여, query에도 values를 전달을 해줍니다.

이때 물음표 하나의 자리에 values를 각각 넣어주어야하기 때문에 이 자리에 이중배열 전체를 넣어주기 위해 [values]로 감싸야 이중배열이 그대로 물음표 하나에 들어갈 수 있습니다.

---

 

⚠️현재 문제(비동기 처리)

현재의 코드의 의도는, delivery를 INSERT -> 그 정보를 가지고 Orders를 INSERT -> 그 정보를 가지고 -> orderedBook을 INSERT 형식입니다.

언뜻봤을 때에는 문제가 없어보이나, 그런데 실행을 했을 때, delivery_id값이 없는 값이라는 오류가 발생합니다. 

// 주문 하기    
const order = (req, res) => {
    const {items, delivery, totalQuantity, totalPrice, userId, firstBookTitle} = req.body;

    let delivery_id;
    let order_id;

    let sql = "INSERT INTO delivery (address, receiver, contact) VALUES (?, ?, ?)";
    let values = [delivery.address, delivery.receiver, delivery.contact];

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

    })  

    sql = `INSERT INTO orders (book_title, total_quantity, total_price, user_id, delivery_id) 
            VALUES (?, ?, ?, ?, ?);`;
    values = [firstBookTitle, totalQuantity, totalPrice, userId, delivery_id];
    conn.query(sql, values, 
        (err, results) => {
            if(err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end(); 
            }

            order_id = results.insertId; 
    })

    sql = `INSERT INTO orderedBook(order_id, book_id, quantity)
            VALUES ?;`;

    //items.. 배열 : 요소들을 하나씩 꺼내서 (foreach문 돌려서)
    values = [];
    items.forEach((items) => {
        values.push([order_id, items.book_id, items.quantity]);
    })
    conn.query(sql, [values], 
        (err, results) => {
            if(err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end(); 
            }

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

values.push([order_id, items.book_id, items.quantity]); 를 실행하였을때, delivery_id와 order_id가 아직 값이 아니라는 겁니다. 

 

🤔 왜 그런거지?

 

conn.query(INSERT delivery, callback1)

conn.query(INSERT orders, callback2)

conn.query(INSERT orderedBook, callback3)

이 세 쿼리는 순차 실행이 아닙니다. 

 

Node.js는:

• 첫 번째 query 실행 요청
• 기다리지 않음
• 바로 두 번째 query 실행
• 또 기다리지 않음
• 세 번째 query 실행

즉, delivery INSERT가 끝나기 전에 orders INSERT가 실행된다는 것입니다!

 

https://ilove-ya.tistory.com/158

[Node.js] Node.js의 비동기 처리 - Promise / Async&await

그리하여 비동기 처리, Promise / Async-await 를 공부하고 왔습니다. 

 

mysql에서는 query를 promise 객체로 감싸줄 수 있는 기능이 있습니다. 

공식 사용법 링크 : https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper

Promise Wrappers | Quickstart

제시된 예시 코드 -> 이걸 활용해 보겠습니다. 

async function example1() {
  const mysql = require('mysql2/promise');
  const conn = await mysql.createConnection({ database: test });
  const [rows, fields] = await conn.execute('select ?+? as sum', [2, 2]);
  await conn.end();
}

async function example2() {
  const mysql = require('mysql2/promise');
  const pool = mysql.createPool({ database: test });
  // execute in parallel, next console.log in 3 seconds
  await Promise.all([
    pool.query('select sleep(2)'),
    pool.query('select sleep(3)'),
  ]);
  console.log('3 seconds after');
  await pool.end();
}

->

 

주문하기 API 

// 주문 하기    
const order = async (req, res) => {
    const conn = await mariadb.createConnection({
            host: '127.0.0.1',
            user : 'root',
            password : 'root',
            database : 'BookShop',
            dateStrings : true
    });

    const {items, delivery, totalQuantity, totalPrice, userId, firstBookTitle} = req.body;

    // delivery 테이블 삽입
    let sql = "INSERT INTO delivery (address, receiver, contact) VALUES (?, ?, ?)";
    let values = [delivery.address, delivery.receiver, delivery.contact];
    let [results] = await conn.execute(sql, values);
    let delivery_id = results.insertId;   

    //orders 테이블 삽입 
    sql = `INSERT INTO orders (book_title, total_quantity, total_price, user_id, delivery_id) 
            VALUES (?, ?, ?, ?, ?);`;
    values = [firstBookTitle, totalQuantity, totalPrice, userId, delivery_id];
    [results] = await conn.execute(sql, values);
    let order_id = results.insertId;


    // items를 가지고, 장바구니에서 book_id, quantity 조회
    sql = `SELECT book_id, quantity FROM cartItems WHERE id IN (?);`;
    let [orderItems, fields] = await conn.query(sql, [items]);
    

    //orderedBook 테이블 삽입
    sql = `INSERT INTO orderedBook(order_id, book_id, quantity) VALUES ?;`;

    //items.. 배열 : 요소들을 하나씩 꺼내서 (foreach문 돌려서)
    values = [];
    orderItems.forEach((item) => {
        values.push([order_id, item.book_id, item.quantity]);
    })

    results = await conn.query(sql, [values]);

    let result = await deleteCartItems(conn, items);

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

const deleteCartItems = async (conn, items) => {
    let sql = `DELETE FROM cartItems WHERE id IN (?); `;

    let result = await conn.query(sql, [items]);
    return result;
}

주문 전체 목록 조회 

const getOrders = async (req, res) => {
    const conn = await mariadb.createConnection({
            host: '127.0.0.1',
            user : 'root',
            password : 'root',
            database : 'BookShop',
            dateStrings : true
    });

    let sql = `SELECT orders.id, created_at,address, receiver, contact,  book_title, total_quantity, total_price
                FROM orders LEFT JOIN delivery 
                ON orders.delivery_id = delivery.id;`

    let [rows, fields] = await conn.query(sql);
    return res.status(StatusCodes.OK).json(rows);
}

 

주문 상세 목록 조회 

const getOrderDetail = async (req, res) => {
    const {id} = req.params;

    const conn = await mariadb.createConnection({
            host: '127.0.0.1',
            user : 'root',
            password : 'root',
            database : 'BookShop',
            dateStrings : true
    });

    let sql = `SELECT book_id, title, author, price, quantity
            FROM orderedBook LEFT JOIN books 
            ON orderedBook.book_id = books.id
            WHERE order_id = ?;`
    let [rows, fields] = await conn.query(sql, [id]);
    return res.status(StatusCodes.OK).json(rows);
}