sequelize raw query

구현하려는 내용

• recruitCount는 User와 Post를 LEFT JOIN ON User.id=Post.hostId를 한 후 COUNT(hostId)를 구한다.
• guestCount는 User와 Post를 LEFT JOIN ON User.id=Post.guestId를 한 후 COUNT(guestId)를 구한다.

작성한 코드

//sequelize 문법
const { count, rows } = await User.findAndCountAll({
  attributes: [
    "id",
    "email",
    "nickname",
    [
      sequelize.fn("COUNT", sequelize.col("`hosts`.`hostId`")),
      "recruitCount",
    ],
    [
      sequelize.fn("COUNT", sequelize.col("`guests`.`guestId`")),
      "guestCount",
    ],
  ],
  include: [
    {
      model: Post,
      as: "hosts",
      attributes: [],
    },
    {
      model: Post,
      as: "guests",
      attributes: [],
    },
  ],
  group: ["User.id"],
});

//sql문
SELECT Users.id, email, nickname, COUNT(hosts.hostId), COUNT(guests.guestId) 
FROM Users LEFT JOIN Posts as hosts 
ON Users.id=hosts.hostId 
LEFT JOIN Posts AS guests 
ON Users.id=guests.guestId 
GROUP BY Users.id;

결과

첫번째 LEFT JOIN을 했을때는 recruitCount가 잘 계산되었지만, 두번째 LEFT JOIN을 한 후에 recruitCount와 guestCount 모두 값이 변경되었다.

해결

서브쿼리를 사용해 hostId로 Grouping된 테이블 A를 만들어 User와 LEFT JOIN시켰고, 두번째 서브쿼리를 사용해 guestId로 Grouping된 테이블 B를 만들어 LEFT JOIN 시켜주었다.

const { Op, QueryTypes } = require("sequelize");
const { User, Post, sequelize } = require("../../models");

const sql =
"SELECT Users.id, Users.email, Users.nickname, IFNULL(A.hostNum,0) AS hostNum, IFNULL(B.guestNum,0) AS guestNum 
FROM Users 
LEFT JOIN (SELECT hostId, COUNT(id) AS hostNum FROM Posts GROUP BY hostId) as A 
ON Users.id=A.hostId 
LEFT JOIN (SELECT guestId, COUNT(id) AS guestNum FROM Posts GROUP BY guestId) AS B 
ON Users.id=B.guestId";

const rows = await sequelize.query(sql, { type: QueryTypes.SELECT });