sql - Sequelize js获取关联模型的计数

Question

例如，我有一个discussions 表和一个replies 表。

如何获取每条讨论记录的回复数？

Models.course_discussions.count({
    where: { 'course_id': 105 },
    include: [
        {model: Models.course_discussions_replies, as: 'replies'}
    ]
})
.then(function (discussions) {
    if (!discussions) {
        reply(Hapi.error.notFound());
    } else {
        console.log("91283901230912830812 " , discussions);
    }
});

上面的代码转换成下面的查询——

SELECT COUNT(`course_discussions`.`id`) as `count` FROM `course_discussions` LEFT OUTER JOIN `course_discussions_replies` AS `replies` ON `course_discussions`.`id` = `replies`.`discussion_id` WHERE `course_discussions`.`course_id`=105;

上面的代码让我计算了讨论次数。但是我如何计算每个讨论的回复数？

下面的 sql 查询是可以的，但是我该如何写成 sequelize 的方式呢？

SELECT COUNT(  `replies`.`discussion_id` ) AS  `count` 
FROM  `course_discussions` 
LEFT OUTER JOIN  `course_discussions_replies` AS  `replies` ON  `course_discussions`.`id` =  `replies`.`discussion_id` 
WHERE  `course_discussions`.`course_id` =105

Answer 1

如果您需要讨论和回复计数，.replies.length 应该适合您。

Models.course_discussions.findAll(
  where: { 'course_id': 105 },
  include: [
    {model: Models.course_discussions_replies, as: 'replies'}
  ]
})
.then(function (discussions) {
  // each discussion in discussions will have a
  // replies array

  discussions[0].repies.length // replies for current discussion
});