[Node.js + SQL] 열심히 끙끙댔던 SQL 쿼리, 코드 모음

이번에 처음으로 SQL을 써보면서 알게 된 여러 경험들을 정리해본다. 사아아실 sql을 깊이 공부해본 적이 없어서 약간의 꼼수와 야매가 가득 섞여있다.

 

1. Select 절에서 서브쿼리 사용

const selectItemsByCount = async (connection, limit, offset) => {
    const selectItemsByCountQuery = `
                SELECT  (select count(*) from wish where wish.item_id = item.item_id) wish_count,
                		item.item_id, item.title, item.price, item.safety_pay, item.location, item.created_at, image.image_path
                FROM item
                INNER JOIN image
                on image.item_id = item.item_id
                WHERE item.status = 1
                GROUP BY image.item_id
                ORDER BY item.view DESC
                LIMIT ?
                OFFSET ?;
    `;
    const [itemsByCountRows] = await connection.query(selectItemsByCountQuery, [limit, offset]);
    return itemsByCountRows;
};

 

where 안에서만 서브쿼리를 사용했었는데, select 안에서도 서브쿼리를 사용할 수 있었다. 이게 더 쉽네. 그냥 똑같이 select 쿼리를 만들어서 Select 절 안에 집어넣는다. 해당 데이터로 로우가 어떤 이름으로 만들어질지 이름을 정해준다. (wish_count)

 

 

2. 매번 다른 조건으로 Select 하기

카테고리별로 상품들을 가져올 때 제일 하위 카테고리는 그냥 카테고리ID 맞는거만 가져오면 되지만, 상위 카테고리인 경우에는 모든 하위 카테고리의 상품들을 한번에 가져와야 한다.

 

const readItemsByCategory = async (categoryId, limit, offset) => {
    const connection = await pool.getConnection(async (conn) => conn);
    const searchCategoryIdArray = [];
    //카테고리가 상위 카테고리면 하위 카테고리들 id 전부 가져오기
    const isParent = await itemDao.parentCategoryCheck(connection, categoryId);
    if (isParent.length < 1) {
        return errResponse(baseResponse.WRONG_CATEGORY_ID);
    }
    if (!isParent[0].parent_id) {
        const childCategoryIdRow = await itemDao.selectChildCategoryId(connection, categoryId);
        childCategoryIdRow.forEach((v) => {
            searchCategoryIdArray.push(v.category_id);
        });
        console.log(searchCategoryIdArray);
    } else {
        searchCategoryIdArray.push(categoryId);
    }

    const ItemsByCategoryRow = await itemDao.selectItemsByCategory(connection, searchCategoryIdArray, limit, offset);
    connection.release();
    return response(baseResponse.SUCCESS, ItemsByCategoryRow);
};

provider.js의 코드. 요청에서 받은 카테고리가 상위 카테고리면 하위 카테고리의 id들을 가져와서 배열에 넣었다.

 

const selectItemsByCategory = async (connection, categoryIdArray, limit, offset) => {
    const array = categoryIdArray.join(',');
    const selectItemsByCategoryQuery = `
                SELECT item.item_id, item.title, item.price, item.safety_pay, item.location, item.created_at, image.image_path, (select count(*) from wish where wish.item_id = item.item_id) wish_count
                FROM item
                INNER JOIN image
                on image.item_id = item.item_id
                WHERE category_id IN (${array}) AND item.status = 1 AND item.sale = "Sale"
                GROUP BY image.item_id
                ORDER BY item.view DESC
                LIMIT ?
                OFFSET ?;
    `;
    const [itemsByCategoryRows] = await connection.query(selectItemsByCategoryQuery, [limit, offset]);
    return itemsByCategoryRows;
};

그 다음에 배열을 ','로 join 해서 WHERE IN 에 넣어주었다. category_id에서 (?,?,?,?..)가 포함된 상품들만 가져온다.

 

 

3. GROUP_CONCAT

const selectReviews = async (connection, userId, limit, offset) => {
    const selectReviewsQuery = `
    SELECT , GROUP_CONCAT(review_image.image_path) as review_image, review.review_id, review.rank, review.content, user.user_id, user.shop_name, user.image as user_image, deal.item_id, review.created_at
    FROM review
    INNER JOIN deal
    ON deal.deal_id = review.deal_id
    INNER JOIN user
    ON user.user_id = deal.buyer_id
    LEFT JOIN review_image
    ON review_image.review_id = review.review_id
    WHERE deal.seller_id = ? AND review.status = 1
    LIMIT ?
    OFFSET ?;
    `;
    const [selectReviewsRows] = await connection.query(selectReviewsQuery, [userId, limit, offset]);
    return selectReviewsRows;
};

GROUP_CONCAT을 사용하면 값이 여러개 나오는 것들을 콤마(,)로 묶어서 하나의 문자열로 받아올 수 있다.

 

 

4. 해당 문자열을 포함한 값 찾기

상품 검색을 위한 부분이다. 검색어 자동완성을 위해 쓰는데 실제 어플에서 쓰는 것 만큼 정교하게는 못하고.. 대신 비슷하게라도 구현해보았다. 인풋에 값이 바뀔 때마다 매번 api요청을 보내서 사용하도록 만들었다.

 

const retrieveItemList = async (searchFor) => {
    const connection = await pool.getConnection(async (conn) => conn);
    const searchParamsList = searchFor.split(' ');
    let searchParamsString = searchParamsList[0];
    searchParamsList.forEach((v, i) => {
        if (i == 0) return;
        searchParamsString = searchParamsString + '|' + v;
    });
    const selectItemListRow = await itemDao.selectItemListPre(connection, searchParamsString); //배열로 검색하는거 추가하기
    console.log(selectItemListRow);
    connection.release();
    return selectItemListRow;
};

먼저 provider 코드. '에어팟 프로'로 검색어가 들어오면 '에어팟'과 '프로'가 포함된 값으로 select 한다. 정규표현식을 이용해서 검색할거기 때문에 '에어팟|프로'의 형태로 문자열을 만들어서 dao 함수로 넘겨준다.

 

const selectItemListPre = async (connection, searchParams) => {
    const selectItemListPreQuery = `
        SELECT item_id, title
        FROM item
        WHERE item.title REGEXP ? AND item.status = 1
        LIMIT 20;
    `;
    const [selectItemListPreRows] = await connection.query(selectItemListPreQuery, searchParams);
    return selectItemListPreRows;
};

SELECT의 WHERE 절에서 정규표현식을 사용할 수 있다. REGEXP 에어팟|프로 로 들어가면 '에어팟' 또는 '프로'가 포함된 값들을 가져온다.