如何在Sequelize中按主题获取各用户的最佳游戏会话

需求：基于Node.js+Sequelize开发答题游戏后端，实现按主题获取排行榜功能，每个用户仅返回该主题下最高分的游戏会话，需包含username、score、count_indicators、errors字段。

以下是两种基于Sequelize原生方法的优化实现，替代原有的多子查询方案：

方案一：使用窗口函数（推荐，仅一次查询） #

利用数据库窗口函数ROW_NUMBER()按用户分组、分数降序排序，直接筛选每个用户的最高分会话，适合PostgreSQL、MySQL 8+等支持窗口函数的数据库：

async getAllBestPlayByTheme(req, res) { 
  try {
    const theme_id = req.params.id;
    const theme = await Theme.findByPk(theme_id);
    if(!theme){
      return res.status(404).json({error: 'Theme not found'});
    }

    const bestPlays = await Play.findAll({
      attributes: [
        'score', 'errors', 'count_indicators',
        // 按用户分组，分数降序生成行号
        [Sequelize.literal('ROW_NUMBER() OVER (PARTITION BY "Play"."user_id" ORDER BY "Play"."score" DESC)'), 'row_num']
      ],
      include: [{
        model: User,
        as: 'player',
        attributes: ['username']
      }],
      where: { theme_id },
      // 保留每个用户的第一条记录（最高分）
      having: Sequelize.where(Sequelize.literal('row_num'), '=', 1),
      order: [['score', 'DESC']],
      nest: true
    });

    // 移除行号字段，格式化输出
    const formattedResult = bestPlays.map(item => {
      const { row_num, ...rest } = item.toJSON();
      return rest;
    });

    if (formattedResult.length === 0) {
      return res.status(404).json({ message: "Score not found" });
    }
    res.json(formattedResult);
  } catch (error) {
    console.error('Error in bestPlays : ', error);
    res.status(500).json({ message: "Error to get score" });
  }
}

方案二：子查询关联（兼容旧版数据库） #

先查询每个用户在指定主题下的最高分，再关联Play表筛选对应会话，适合不支持窗口函数的旧版数据库：

async getAllBestPlayByTheme(req, res) { 
  try {
    const theme_id = req.params.id;
    const theme = await Theme.findByPk(theme_id);
    if(!theme){
      return res.status(404).json({error: 'Theme not found'});
    }

    // 子查询：获取每个用户的最高分
    const userMaxScores = await Play.findAll({
      attributes: ['user_id', [Sequelize.fn('MAX', Sequelize.col('score')), 'maxScore']],
      where: { theme_id },
      group: ['user_id'],
      raw: true
    });

    // 构建匹配条件：用户ID+最高分+主题ID
    const matchConditions = userMaxScores.map(score => ({
      user_id: score.user_id,
      score: score.maxScore,
      theme_id
    }));

    // 查询对应会话并关联用户信息
    const bestPlays = await Play.findAll({
      attributes: ['score', 'errors', 'count_indicators'],
      include: [{
        model: User,
        as: 'player',
        attributes: ['username']
      }],
      where: { [Sequelize.Op.or]: matchConditions },
      order: [['score', 'DESC']],
      nest: true
    });

    if (bestPlays.length === 0) {
      return res.status(404).json({ message: "Score not found" });
    }
    res.json(bestPlays);
  } catch (error) {
    console.error('Error in bestPlays : ', error);
    res.status(500).json({ message: "Error to get score" });
  }
}

方案对比 #

• 方案一：仅需一次数据库查询，性能更优，代码简洁，依赖数据库对窗口函数的支持。
• 方案二：兼容性更强，适合旧版数据库，逻辑直观但需两次查询。

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