Raw SQL 쿼리를 Sequelize ORM 쿼리로 바꾸는 방법

Raw SQL 쿼리를 활용해 원하는 레코드의 컬럼을 가지고 다양한 평균값을 내려고 했는데
동작이 안되더라고요! 그래서 Sequelize ORM 쿼리를 활용해 함수를 다시 만들었습니다

 

🤔 Sequelize ORM 쿼리를 활용해 만든 함수의 장점
- 간결하다
- 이해가 쉽다
- 유지보수가 쉽다 

 

 

Raw SQL 쿼리를 사용한 코드

async getTeamAvg(team_id) {
  try {
    const result = await db.sequelize.query(
      `SELECT
        AVG(ac.job_requirement_score) AS team_job_req_avg,
        AVG(ac.job_autonomy_score) AS team_job_auto_avg,
        AVG(ac.relationship_conflict_score) AS team_rel_conf_avg,
        AVG(ac.job_instability_score) AS team_job_inst_avg,
        AVG(ac.organization_system_score) AS team_org_sys_avg,
        AVG(ac.compensation_adequate_score) AS team_comp_ade_avg,
        AVG(ac.job_culture_score) AS team_job_cul_avg,
        AVG(ac.total_score) AS team_total_avg
      FROM after_cares ac
      JOIN users u ON ac.consultation_user_id = u.id
      WHERE u.team_id = :team_id
      AND YEAR(ac.examined_on) = YEAR(CURDATE());`,
      {
        replacements: { team_id },
        type: db.sequelize.QueryTypes.SELECT,
        raw: true // Ensure raw data without metadata
      }
    );

    // Return the first result from the array
    return result[0] || {}; // Return an empty object if no result
  } catch (error) {
    console.error("Error in getTeamAvg:", error);
    throw error;
  }
}

 

Sequelize ORM 쿼리를 사용한 코드

async getTeamAvg(team_id, year) {
    const yearBetween = getStartAndEndYearWithoutTime(year);
    try {
      const result = await db.after_cares.findAll({
        attributes: [
          [db.sequelize.fn("AVG", db.sequelize.col("job_requirement_score")), "team_job_req_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("job_autonomy_score")), "team_job_auto_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("relationship_conflict_score")), "team_rel_conf_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("job_instability_score")), "team_job_inst_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("organization_system_score")), "team_org_sys_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("compensation_adequate_score")), "team_comp_ade_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("job_culture_score")), "team_job_cul_avg"],
          [db.sequelize.fn("AVG", db.sequelize.col("total_score")), "team_total_avg"],
        ],
        where: {
          consultation_team_id: team_id,
          examined_on: {
            [Op.between]: [yearBetween.start, yearBetween.end],
          },
        },
        raw: true,
      });
      return result[0] || {};
    } catch (error) {
      console.error("Error in getTeamAvg:", error);
      throw error;
    }
  }

 

 

---

 

변환하는 방법

예 ) 변환할 Raw SQL 쿼리

SELECT AVG(ac.job_requirement_score) AS team_job_req_avg
FROM after_cares ac
JOIN users u ON ac.consultation_user_id = u.id
WHERE u.team_id = :team_id
AND YEAR(ac.examined_on) = YEAR(CURDATE());

-- 넘겨받은 team_id와 동일한 값을 가진 users 테이블의 사용자가 연결된 after_cares 데이터 중, examined_on의 연도가 현재 연도인 레코드의 job_requirement_score 평균을 계산하는 쿼리

 

 

Raw SQL 쿼리 변환하는 과정 (Step-by-Step)

(1) SELECT 구문을 attributes로 변환

SELECT에서 계산할 필드는 Sequelize에서 attributes 속성을 사용하여 지정한다
예를 들어, AVG(ac.job_requirement_score) 같은 SQL 구문은 sequelize.fn 함수를 사용하여 정의

attributes: [
  [sequelize.fn('AVG', sequelize.col('job_requirement_score')), 'team_job_req_avg']
]

 

(2) JOIN 구문을 include로 변환

Sequelize ORM에서는 테이블을 조인할 때 include 옵션을 사용한다
JOIN users u ON ac.consultation_user_id = u.id 구문은 다음과 같이 변환

include: [{
  model: Users,
  where: { team_id: team_id },  // 조인 조건
  attributes: [],  // users 테이블에서 필요한 속성들
}]

 

(3) WHERE 절을 where 옵션으로 변환

WHERE 구문은 where 옵션으로 정의한다
특히 YEAR(CURDATE())와 같은 날짜 필터링은 Sequelize의 Op 연산자를 사용하여 처리할 수 있다

where: {
  examined_on: {
    [Op.between]: [startYear, endYear],  // 년도를 특정 범위로 필터링
  },
},

 

(4) 최종 코드

async function getTeamAvg(team_id) {
  const year = new Date().getFullYear();
  const { start, end } = getStartAndEndYearWithoutTime(year); // util에 있는 특정 년도의 시작과 끝 범위를 반환해주는 함수

  try {
    const result = await AfterCares.findAll({
      attributes: [
        [sequelize.fn('AVG', sequelize.col('job_requirement_score')), 'team_job_req_avg']
      ],
      include: [{
        model: Users,
        where: { team_id: team_id },  // 조인 조건
        attributes: [],
      }],
      where: {
        examined_on: {
          [Op.between]: [start, end],  // 날짜 범위 필터링
        },
      },
      raw: true,
    });

    return result;
  } catch (error) {
    console.error("Error in getTeamAvg:", error);
    throw error;
  }
}