如何在@nestjs/sequelize中实现带子查询的JOIN获取电影评分

以下是对应目标SQL的@nestjs/sequelize实现代码：

import { Op, fn, col } from 'sequelize';
import { Rating } from './path-to-rating.model'; // 替换为你的Rating模型实际路径

// ...

return this.movieRepo.findAndCountAll({
  where: {
    title: { [Op.iLike]: '%Titanic%' },
  },
  attributes: {
    include: [
      [fn('AVG', col('rating.score')), 'avg_score'],
      [fn('COUNT', col('rating.userId')), 'rating_count'],
    ],
  },
  include: [
    {
      model: Rating,
      as: 'rating', // 需与Movie和Rating模型间关联定义的别名保持一致
      attributes: [],
      required: false, // 对应原SQL的LEFT JOIN
      group: [col('movie.id')],
    },
  ],
  group: ['movie.id'],
  limit: 10,
  raw: true, // 可选：返回扁平化JSON数据而非Sequelize模型实例
});

关键说明 #

• WHERE条件：通过Op.iLike实现原SQL中不区分大小写的模糊匹配逻辑。
• 聚合计算：使用fn调用SQL原生的AVG和COUNT函数，结合col指定关联表字段，同时为统计结果设置别名便于后续使用。
• 左连接配置：include中设置required: false实现左连接，attributes: []避免返回Rating表的冗余原始字段。
• 分组规则：内外层均按movie.id分组，确保聚合函数能正确计算每部电影的评分统计值。

内容的提问来源于stack exchange，提问作者Kirill Dyachkovskiy