역정규화 컬럼 관리

폴더 개수 역정규화 컬럼 갱신 쿼리

SELECT  id,
        product_count,
        COALESCE(x.cnt, 0) AS expected
FROM product_folder p
    LEFT JOIN (
    SELECT product_folder_id, COUNT(*) AS cnt
    FROM product
    where deleted_at IS NULL
    GROUP BY product_folder_id
    ) x ON x.product_folder_id = p.id
WHERE p.product_count <> COALESCE(x.cnt, 0);

UPDATE product_folder p
    LEFT JOIN (
        SELECT product_folder_id, COUNT(*) AS cnt
        FROM product
        where deleted_at IS NULL
        GROUP BY product_folder_id
    ) x ON x.product_folder_id = p.id
SET p.product_count = COALESCE(x.cnt, 0);

상품 이미지 개수 역정규화 컬럼 갱신 쿼리

SELECT  id,
        image_count,
        COALESCE(x.cnt, 0) AS expected
FROM product p
    LEFT JOIN (
    SELECT product_id, COUNT(*) AS cnt
    FROM product_image
    where deleted_at IS NULL
    GROUP BY product_id
    ) x ON x.product_id = p.id
WHERE p.image_count <> COALESCE(x.cnt, 0);

UPDATE product p
    LEFT JOIN (
        SELECT product_id, COUNT(*) AS cnt
        FROM product_image
        where deleted_at IS NULL
        GROUP BY product_id
    ) x ON x.product_id = p.id
SET p.image_count = COALESCE(x.cnt, 0);