LEFT JOIN 시 행 중복 문제 해결하기

0. 예시로 들 테이블

products, product_images, charges, product_charges

 

1. 행 중복 문제

LEFT JOIN으로 여러 테이블을 조인하면 겹치는 행이 나올 수 있다.

예를 들어 products, product_images, charges 세 개 테이블을 LEFT JOIN하면,

SELECT
   products.id, products.title, products.description, products.price,
   product_images.url,
   charges.name, charges.price
FROM products 
LEFT JOIN product_images ON products.id = product_images.product_id
LEFT JOIN product_charges ON products.id = product_charges.product_id
LEFT JOIN charges ON charges.id = product_charges.charge_id;

행이 중복되어 나오는 문제가 발생한다. 

1번 product의 첫번째 이미지 url에 배송비를 연결해서 한 행,

1번 product의 첫번째 이미지 url에 포장비를 연결해서 한 행,  

1번 product의 두번째 이미지 url에 배송비를 연결해서 한 행,

1번 product의 두번째 이미지 url에 포장비를 연결해서 한 행,  

총 네 행이나 나와버린다.

테이블을 두 개 join한다면 GROUP BY로 해결할 수 있지만,

세 개부터는 GROUP BY로 해결할 수 없다.

GROUP BY를 사용해봤자 다음과 같은 결과가 나온다.

SELECT
   products.id, products.title, products.description, products.price,
   JSON_ARRAYAGG(product_images.url),
   JSON_ARRAYAGG(charges.name), JSON_ARRAYAGG(charges.price)
FROM products 
LEFT JOIN product_images ON products.id = product_images.product_id
LEFT JOIN product_charges ON products.id = product_charges.product_id
LEFT JOIN charges ON charges.id = product_charges.charge_id
GROUP BY products.id;

이 문제를 해결하려면 db에서 sub-query를 사용하거나, 애플리케이션에서 join을 해주어야 한다.

 

2. sub-query

서브쿼리는 독립적으로도 실행 가능한 쿼리이다.

서브쿼리는 쿼리 내에서 일부분의 데이터를 가져올 때 사용한다.

SELECT
   products.id, products.title, products.description, products.price,
   pi.images,
   pc.charges
FROM products
LEFT JOIN(
   SELECT 
      product_id,
      JSON_ARRAYAGG(
         JSON_OBJECT(
            "id", id,
            "url", url
            )
        ) as images
	FROM product_images
    GROUP BY product_id
) pi ON products.id = pi.product_id
LEFT JOIN (
	SELECT
        product_id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                "id", product_charges.id,
                "name", charges.name,
                "price", charges.price
            )
        ) as charges
	FROM product_charges
	JOIN charges ON product_charges.charge_id = charges.id
    GROUP BY product_id
) pc ON products.id = pc.product_id
GROUP BY products.id;

 

3. Application Join

(node.js를 사용하는 경우)모델단에서 테이블별로 데이터를 가져온다.

그 다음 js에서 가져온 데이터를 합친다.

테이블을 join해서 한 번에 가져오는 것이 아니기 때문에, 

테이블 개수만큼 db에 갔다와야 하는 것이 단점이다.  

대신 디버깅이 sub query를 사용하는 방식보다 쉽다.

 

<productController.js>

const userService = require("../services/userService")

const getProducts = async (req, res) => {
    const filter = {limit: 1, offset: 0};
    const data = await userService.getProducts(filter)
    res.status(201).json({message: data})
}

module.exports = {
    getProducts
}

<productService.js>

const userDao = require("../models/userDao")

const getProducts = async (filter) => {
    const products = await userDao.getProducts(filter); // 상품 객체로 된 배열
    const productIds = products.map(product => product.id); // 상품 id로 된 배열
    const productImages = await userDao.getProductImages(productIds); // 상품 이미지 객체로 된 배열
    const productCharges = await userDao.getProductCharges(productIds);

    const productImagesObject = {};
    productImages.forEach(pi => productImagesObject[pi.productId] = pi.images);
    
    const productChargesObject = {};
    productCharges.forEach(pc => productChargesObject[pc.productId] = pc.charges);

    const joinedProducts = products.map((product) => ({
        ...product,
        images: productImagesObject[product.id],
        charges: productChargesObject[product.id]
    }));
    
    return joinedProducts;
}

module.exports = { getProducts }

<productDao.js>

const { DataSource } = require('typeorm');

const myDataSource = new DataSource({
    type: process.env.TYPEORM_CONNECTION,
    host: process.env.TYPEORM_HOST,
    port: process.env.TYPEORM_PORT,
    username: process.env.TYPEORM_USERNAME,
    password: process.env.TYPEORM_PASSWORD,
    database: process.env.TYPEORM_DATABASE
})

myDataSource.initialize()
.then(() => {
    console.log("Data Source has been initialized")
})
.catch(() => {
    console.log("Database initiate fail")
})


const getProducts = async (filter) => {
    const { limit, offset } = filter
    const products = await myDataSource.query(
        `SELECT 
           id, title, description, price
        FROM 
           products
        ${limit ? `limit ${limit}` : " "}
        ${offset ? `offset ${offset}` : " "}    
    `); // 이런 형태가 된다 SELECT * FROM products limit 1 offset 0;
    return products
}

const getProductImages = async (productIds) => {
    const productImages = await myDataSource.query(
        `SELECT 
           product_id as productID,
           JSON_ARRAYAGG(
               JSON_OBJECT(
                   "id", product_images.id,
                   "url", url
               )
           ) as images
         FROM 
            product_images
         WHERE 
            product_id in (${Array(productIds.length).fill("?").join(",")})`, 
        productIds);
    return productImages 
}

const getProductCharges = async(productIds) => {
    const charges = await myDataSource.query(
        `SELECT 
            product_id as productId,
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    "id", charges.id,
                    "name", charges.name,
                    "price", charges.price
                )
            ) as charges
        FROM product_charges
        JOIN charges ON charges.id = product_charges.charge_id
        WHERE 
           product_id in (${Array(productIds.length).fill("?").join(",")})
        GROUP BY
           productId;
    `, productIds);
    return charges;
}

module.exports = { getProducts, getProductImages, getProductCharges }

 

 

참고

justcode - [Node + express 톺아보기] SQL Advanced