실제 서비스에 들어가는건 아니지만 쇼핑몰 구축하는중 상품은 옵션이 있다는걸 깜빡하고 테이블을 설계했습니다. 작업환경은 MySQL, Sequelize.js를 사용하고 있습니다. 아직까지는 faker로 페이크데이터만 만들었습니다만. 상품 색상과 사이즈를 그냥 생각없이 랜덤한 문자열로만 넣어서 상품 색상과 사이즈는 단일 색상, 단일 사이즈가 되버렸습니다. 실제 서비스같이 만들려면 다채로운 색상과 사이즈가 필요합니다. 뷰로 보내줄때는 배열로 보내주는게 프론트에서 사용하기에 편하겠죠. 그래서 페이크 데이터를 다음과 같이 만들어지도록 바꿔봤습니다.

productColor: [
  faker.commerce.color(),
  faker.commerce.color(),
  faker.commerce.color(),
  faker.commerce.color(),
  faker.commerce.color()
  ],

  (...)

MySQL에서는 컬럼에 배열로 저장이 안된다는걸 모른 저는 저런 멍청한 짓을 해버렸습니다. 저대로 페이크데이터를 입력하려고 하니 Sequelize에서는 유효성 에러가 뜨더군요. 배열이나 객체는 필드에 올 수가 없다고. 맞습니다. MySQL에서는 안된다더군요. PostgreSQL에서는 배열도 JSON데이터도 저장이 된다고 합니다. 게다가 둘간의 마이그레이션이 생각보다 간단하다니 다음에 기회가 된다면 써봐야겠습니다.

개발환경에서 데이터베이스를 건들이기엔 뒷 일이 걱정돼 먼저 콘솔에서 MySQL에 접속해서 먼저 테스트해보고 개발환경으로 넘어가기로 합니다. 참고로 색상이나 사이즈는 유니크하지 않기 때문에 따로 참조를 걸지 않습니다.

-- 데이터베이스를 추가합니다.
CREATE DATABASE test;

-- 상품, 색상, 사이즈 테이블을 추가합니다.
CREATE TABLE products
(
    p_id INT,
    p_name VARCHAR(32),
    PRIMARY KEY (p_id)
);

CREATE TABLE color
(
    p_id INT,
    p_color VARCHAR(24)
);

CREATE TABLE size
(
    p_id INT,
    p_size VARCHAR(12)
);

-- 테이블 별로 데이터를 삽입합니다.
INSERT INTO products
VALUES
(1, 'test1'),
(2, 'test2'),
(3, 'test3'),
(4, 'test4'),
(5, 'test5');

INSERT INTO color
VALUES
(1, 'black'),
(1, 'white'),
(1, 'gray'),
(2, 'black'),
(2, 'beige'),
(3, 'blue'),
(3, 'red'),
(3, 'green'),
(4, 'black'),
(4, 'white'),
(4, 'beige'),
(4, 'gray'),
(4, 'mustard'),
(5, 'black'),
(5, 'white');

INSERT INTO size
VALUES
(1, 'S'),
(1, 'M'),
(1, 'L'),
(1, 'XL'),
(2, 'Free'),
(3, 'XS'),
(3, 'S'),
(3, 'M'),
(3, 'L'),
(3, 'XL'),
(4, 'M'),
(4, 'L'),
(4, 'XL'),
(5, 'Free');

테이블 설계 및 데이터 삽입을 마쳤으면 sql 쿼리문을 작성합니다. 처음에는 상품마다 색상, 사이즈가 다 있기 때문에 FULL OUTER JOIN을 생각했는데 문법 오류가 뜨더군요. 그래서 LEFT JOIN을 했는데 원하는 결과가 나왔습니다.

SELECT p.p_id, p.p_name, s.p_size, c.p_color
FROM products AS p
LEFT JOIN size AS s
ON p.p_id = s.p_id
LEFT JOIN color AS c
ON p.p_id = c.p_id;

쿼리문을 실행하면 다음과 같이 하나의 상품에 들어갈 옵션의 모든 경우의 수가 나옵니다.

+------+--------+--------+---------+
| p_id | p_name | p_size | p_color |
+------+--------+--------+---------+
|    1 | test1  | S      | black   |
|    1 | test1  | M      | black   |
|    1 | test1  | L      | black   |
|    1 | test1  | XL     | black   |
|    1 | test1  | S      | white   |
|    1 | test1  | M      | white   |
|    1 | test1  | L      | white   |
|    1 | test1  | XL     | white   |
|    1 | test1  | S      | gray    |
|    1 | test1  | M      | gray    |
|    1 | test1  | L      | gray    |
|    1 | test1  | XL     | gray    |
|    2 | test2  | Free   | black   |
|    2 | test2  | Free   | beige   |
|    3 | test3  | XS     | blue    |
|    3 | test3  | S      | blue    |
|    3 | test3  | M      | blue    |
|    3 | test3  | L      | blue    |
|    3 | test3  | XL     | blue    |
|    3 | test3  | XS     | red     |
|    3 | test3  | S      | red     |
|    3 | test3  | M      | red     |
|    3 | test3  | L      | red     |
|    3 | test3  | XL     | red     |
|    3 | test3  | XS     | green   |
|    3 | test3  | S      | green   |
|    3 | test3  | M      | green   |
|    3 | test3  | L      | green   |
|    3 | test3  | XL     | green   |
|    4 | test4  | M      | black   |
|    4 | test4  | L      | black   |
|    4 | test4  | XL     | black   |
|    4 | test4  | M      | white   |
|    4 | test4  | L      | white   |
|    4 | test4  | XL     | white   |
|    4 | test4  | M      | beige   |
|    4 | test4  | L      | beige   |
|    4 | test4  | XL     | beige   |
|    4 | test4  | M      | gray    |
|    4 | test4  | L      | gray    |
|    4 | test4  | XL     | gray    |
|    4 | test4  | M      | mustard |
|    4 | test4  | L      | mustard |
|    4 | test4  | XL     | mustard |
|    5 | test5  | Free   | black   |
|    5 | test5  | Free   | white   |
+------+--------+--------+---------+
46 rows in set (0.00 sec)

콘솔에서 테스트하는건 이쯤해서 마치도록 하고 개발환경으로 넘어가 콘솔에서 했던 것과 똑같은 세팅으로 테이블을 설계합니다.

models.products.findAll({
  include: [
    // 조인에 조인을 하는게 아니고 각자 LEFT JOIN을 하는 것이니 조인할 테이블을 배열에 객체로 넣어줍니다.
    {
      model: models.size,
      required: false
    },
    {
      model: models.color,
      required: false
    }
  ]
});

자꾸 모르는 컬럼 id가 필드리스트에 없다고 뜹니다. 뭔가하고 쿼리문을 보니 다음과 같았습니다.

SELECT
`p`.`p_id`,
`p`.`p_name`,

`s`.`p_id` AS `s.p_id`,
`s`.`p_id` AS `s.p_id`,
`s`.`p_size` AS `s.p_size`,

`c`.`p_id` AS `c.p_id`,
`c`.`p_id` AS `c.p_id`,
`c`.`p_color` AS `c.p_color`,

FROM `products` AS `p`

LEFT OUTER JOIN `size` AS `s`
ON `p`.`p_id` = `s`.`id` -- 이 부분과

LEFT OUTER JOIN `color` AS `c`
ON `p`.`p_id` = `c`.`id` -- 이 부분

주석 처리된 줄을 보시면 알겠지만 제가 의도한바가 아닙니다. s.p_id가 오는게 제가 의도한 바이니까요. 이건 sequelize-cli의 고질적인 문제같습니다. sequelize model:generate하면 생성되는 migrations파일을 보면 제가 만들지 않은 컬럼들이 알아서 생성되어 있는 것과 관련이 있습니다. PK와 auto increment처리된 id컬럼과 not null처리된 createdAt, updatedAt컬럼이 맘대로 생겨버리거든요. 편할 때도 있지만 지금과 같은 상황에서는 아주 불편한 녀석입니다.

아무튼 sequelize는 기본적으로 id가 지워졌음에도 불구하고 해당 테이블에 PK가 설정된 컬럼이 존재하지 않으면 자동으로 쿼리문에 1순위로 ON조건에 입력이 되버립니다. 이것을 해결하기 위해서는 테이블간의 관계를 association처리를 해주듯이 removeAttribute('id')를 아래와 같이 작성해줘야 합니다. association으로 belongsTo()메소드는 사용하지않고 hasMany()만 사용해서 관계설정도 같이 해줍니다.

size.removeAttribute('id');
color.removeAttribute('id');

products.hasMany(size, {
  foreignKey: 'p_id'
});
products.hasMany(color, {
  foreignKey: 'p_id'
});

그러면 콘솔에 나오는 쿼리에 id가 사라지게 됩니다. 그리고 sequelize에서도 쿼리문을 실행해 다음과 같은 json데이터를 반환합니다. 필두에서 언급했듯이 mysql은 컬럼에 배열을 담을 수가 없습니다. 때문에 p_color 밑에 p_color가 오는 지저분한 구조로 구현할 수밖에 없었습니다. 더 나은 방법을 공부하게 된다면 추후에 글을 수정하도록 하겠습니다.

{
  "products": [
    {
      "p_id": "1",
      "p_name": "test1",
      "p_color": [
        {
          "p_color": "black"
        },
        {
          "p_color": "white"
        },
        {
          "p_color": "gray"
        }
      ],
      "p_size": [
        {
          "p_color": "S"
        },
        {
          "p_color": "M"
        },
        {
          "p_color": "L"
        },
        {
          "p_color": "XL"
        }
      ]
    },
    {
      "p_id": "2",
      "p_name": "test2",
      "p_color": [
        {
          "p_color": "black"
        },
        {
          "p_color": "beige"
        }
      ],
      "p_size": [
        {
          "p_size": "Free"
        }
      ]
    },
    {
      "p_id": "3",
      "p_name": "test3",
      "p_color": [
        {
          "p_color": "blue"
        },
        {
          "p_color": "red"
        },
        {
          "p_color": "green"
        }
      ],
      "p_size": [
        {
          "p_size": "XS"
        },
        {
          "p_size": "S"
        },
        {
          "p_size": "M"
        },
        {
          "p_size": "L"
        },
        {
          "p_size": "XL"
        }
      ]
    },
    {
      "p_id": "4",
      "p_name": "test4",
      "p_color": [
        {
          "p_color": "black"
        },
        {
          "p_color": "white"
        },
        {
          "p_color": "beige"
        },
        {
          "p_color": "gray"
        },
        {
          "p_color": "mustard"
        }
      ],
      "p_size": [
        {
          "p_size": "M"
        },
        {
          "p_size": "L"
        },
        {
          "p_size": "XL"
        }
      ]
    },
    {
      "p_id": "5",
      "p_name": "test5",
      "p_color": [
        {
          "p_color": "black"
        },
        {
          "p_color": "white"
        }
      ],
      "p_size": [
        {
          "p_size": "Free"
        }
      ]
    }
  ]
}

#5193을 참고했습니다. 쿼리문 옵션에 raw: true옵션을 주는 방법이 있습니다. 가지고 있는 모든 객체를 반환할겁니다. 제가 원하던 식의 데이터는 아니지만 괜찮게 나오네요. 모든 객체를 반환한대서 exclude옵션에 들어간 레코드까지 나오는줄 알았습니다만 그건 아닙니다. 반환되는 JSON데이터는 아래와 같습니다. 참고로

{
  "products": [
    {
      "p_id": "1",
      "p_name": "test1",
      "color.p_color": "black",
      "size.p_size": "S"
    }, {
      "p_id": "1",
      "p_name": "test1",
      "color.p_color": "black",
      "size.p_size": "M"
    }, {
      "p_id": "1",
      "p_name": "test1",
      "color.p_color": "black",
      "size.p_size": "L"
    },

    (...)]
}