컨알델 프로젝트 여러개의 서브이미지 조회 11/21

샵 상세 페이지

    <!--  샵 상세 페이지 상단 조회 -->
    <select id="selectProductDetailHeader" parameterType="map" resultType="com.app.bluecotton.domain.dto.ProductDetailResponseDTO">
        SELECT
        TBP.ID,
        TBP.PRODUCT_NAME,
        TBP.PRODUCT_PRICE,
        TBP.PRODUCT_TYPE,
        TBP.PRODUCT_PURCHASE_TYPE,

        (SELECT ROUND(AVG(TBPR.PRODUCT_REVIEW_RATING), 1)
        FROM TBL_PRODUCT_REVIEW TBPR
        WHERE TBPR.PRODUCT_ID = TBP.ID) AS PRODUCT_AVG_RATING,

        (SELECT COUNT(*)
        FROM TBL_PRODUCT_REVIEW TBPR
        WHERE TBPR.PRODUCT_ID = TBP.ID) AS PRODUCT_REVIEW_COUNT,

        (SELECT COUNT(*)
        FROM TBL_PRODUCT_LIKE TBPL
        WHERE TBPL.PRODUCT_ID = TBP.ID) AS PRODUCT_LIKE_COUNT,

        (CASE
        WHEN #{memberId} IS NULL THEN 0
        ELSE (
        SELECT COUNT(*)
        FROM TBL_PRODUCT_LIKE TBPL_MY
        WHERE TBPL_MY.PRODUCT_ID = TBP.ID
        AND TBPL_MY.MEMBER_ID = #{memberId}
        ) END) AS PRODUCT_IS_LIKED,

        (SELECT TBPI.PRODUCT_IMAGE_PATH || TBPI.PRODUCT_IMAGE_NAME
        FROM TBL_PRODUCT_IMAGE TBPI
        WHERE TBPI.PRODUCT_ID = TBP.ID
        AND TBPI.PRODUCT_IMAGE_TYPE = 'MAIN'
        AND ROWNUM = 1) AS PRODUCT_MAIN_IMAGE_URL,

        (SELECT LISTAGG(TBPI.PRODUCT_IMAGE_PATH || TBPI.PRODUCT_IMAGE_NAME, ',')
        WITHIN GROUP (ORDER BY TBPI.ID)
        FROM TBL_PRODUCT_IMAGE TBPI
        WHERE TBPI.PRODUCT_ID = TBP.ID
        AND TBPI.PRODUCT_IMAGE_TYPE = 'SUB') AS PRODUCT_SUB_IMAGE_URL

        FROM TBL_PRODUCT TBP
        WHERE TBP.ID = #{id}
    </select>

여러개의 SUB이미지를 LISTAGG함수를 이용해서 조회하고 있음

        (SELECT LISTAGG(TBPI.PRODUCT_IMAGE_PATH || TBPI.PRODUCT_IMAGE_NAME, ',')
        WITHIN GROUP (ORDER BY TBPI.ID)
        FROM TBL_PRODUCT_IMAGE TBPI
        WHERE TBPI.PRODUCT_ID = TBP.ID
        AND TBPI.PRODUCT_IMAGE_TYPE = 'SUB') AS PRODUCT_SUB_IMAGE_URL

"기존에는 LISTAGG 함수를 사용해 쿼리 한 번으로 이미지를 합쳤으나, 오라클의 문자열 길이 제한(4000byte) 문제와 확장성을 고려하여 리팩토링했습니다.

1:N 관계인 서브 이미지를 별도 쿼리로 분리하고, 서비스 계층(Service Layer)에서 데이터를 조립하는 방식을 선택하여 데이터 안정성을 확보했습니다.