유저테이블(User)과 대회테이블(Contest)이 각각 참가(Participate)와 입상(Win)이라는 n:M 관계를 2개 맺고있습니다.
이때 대회 테이블에는 참가포인트(pPoint), 입상포인트(wPoint)라는 정수형 칼럼을 가지고 있고
유저 정보와 함께 합산 포인트를 포인트를 많이 가지고 있는 순으로 db에서 가져오고 싶습니다.
일단 순차적으로 참가포인트 합, 입상포인트 합을 가져오려고
router.get('/', async (req, res, next) => {
try {
const userList = await User.findAll({
logging: true,
group: ['User.id'],
include: [{
model: Contest,
as: "Participated",
through: { model: Participate, attributes: [] },
attributes: ['pPoint'],
}, {
model: Contest,
as: "Won",
through: { model: Win, attributes: [] },
attributes: ['wPoint'],
}],
attributes: ['id', [fn('sum', col('Participated.pPoint')), 'totalppoint'], [fn('sum', col('Won.wPoint')), 'totalwpoint']],
});
res.json(userList);
} catch (err) {
console.error(err);
next(err);
}
});
와 같이 했는데
생각대로 되지않아 sql log를 찍어봤는데 left outer join이어서 참가수 * 입상수 의 배수로 나오더라고요.
혹시 제 생각대로 db에서 받아올 수는 있는 지가 궁금해지고
만약 안되면 관계테이블에 포인트 칼럼을 추가하고 추후에 포인트 변경을 할 때마다 모든 관계테이블을 수정하는 식으로 설계를 해야하는게 맞겠죠?
지금 구조가
참가 테이블: 유저아이디, 대회아이디
입상 테이블: 유저아이디, 대회아이디
맞죠?
groupBy를 뺐을 때는 유저+참가포인트들 + 입상포인트를 리스트가 제대로 불러와지나요?
답글
권범수
2021.11.15group by를 빼고 sum 값들을 빼면 잘 불러와집니다.
제가 group을 하고 생각대로 안되서 워크벤치에서 쿼리문을 짜서 쿼리를 날려봤는데
우선 예를 들면
유저 테이블 데이터:[{id:1},{id:2},{id:3}]
대회:[{id:1, pPoist: 100, wPoint:200},{id:2, pPoist: 200, wPoint:300},{id:3, pPoist: 300, wPoint:400}]
참가 : [{id:1, userId:1, contestId:1},{id:2, userId:1, contestId:2},{id:3, userId:1, contestId:3}
{id:4, userId:2, contestId:1},{id:5, userId:2, contestId:3},{id:6, userId:3, contestId:2}]
입상: [{id:1, userId:1, contestId:3},{id:2, userId:1, contestId:2}
{id:3, userId:2, contestId:1},{id:4, userId:3, contestId:2}]
이고
위에 사진처럼 했을때
[{"id":1,"totalppoint":"1200","totalwpoint":"2100","Participated":[{"pPoint":100}],"Won":[{"wPoint":300}]},{"id":2,"totalppoint":"400","totalwpoint":"400","Participated":[{"pPoint":100}],"Won":[{"wPoint":200}]},{"id":3,"totalppoint":"200","totalwpoint":"300","Participated":[{"pPoint":200}],"Won":[{"wPoint":300}]}]
를 주고
group과 sum값들을 빼고 쿼리를 날리면
"id":1,"Participated":[{"pPoint":100},{"pPoint":200},{"pPoint":300}],"Won":[{"wPoint":300},{"wPoint":400}]},{"id":2,"Participated":[{"pPoint":100},{"pPoint":300}],"Won":[{"wPoint":200}]},{"id":3,"Participated":[{"pPoint":200}],"Won":[{"wPoint":300}]}]
워크 밴치로 쿼리문 작성해서 실행하면
id | Participated.id | participated.pPoint | won.id | Won.wPoint
제로초(조현영)
2021.11.15participated와 won때문에 문제인 거네요. 동시에 조회하느라 중복되어서 나오네요.
쿼리를 따로따로 두 개 날리든지, 저렇게 날린 후에 프론트에서 합을 다시 계산하는 방법이 편할 것 같습니다. SQL 쿼리 한 번으로 해결하는 방법도 있긴 할 것 같습니다.
권범수
2021.11.15sql로 하는 방법은 서브 쿼리 두번(sum 한 칼럼명은 일치시키고)에 union all 하고 거기서 유저id랑 sum값 group by해서 가져오면 되긴 합니다. 근데 이걸 sequelize로 하는 방법이 있나 해서요. sequelize 에서 로우 쿼리 날리는 방법밖에 없겠죠?
권범수
2021.11.15api reference에서 찾아봐도 서브쿼리에 관한건 있는데 union에 관한건 안나와서..
제로초(조현영)
2021.11.16네, 시퀄라이즈는 union 관련해서는 지원이 없는 것 같습니다.