Sequelize三表多内连接的模型关系定义方案求助

核心关联逻辑 #

一场比赛（Match）对应主队、客队两个赛事队伍（TournamentTeam），每个赛事队伍关联一个基础队伍（Team）的信息。错误根源在于原查询直接从Match关联Team，忽略了TournamentTeam作为中间表的关联作用，需通过嵌套关联实现多表连接。

1. 模型关系配置 #

MatchModel 关联 TournamentTeamModel #

// MatchModel.js
MatchModel.belongsTo(TournamentTeamModel, {
  foreignKey: 'HomeUID', // Match表中存储主队赛事队伍ID的字段
  as: 'HomeTournamentTeam', // 关联别名，查询时需对应使用
  targetKey: 'TournamentTeamUID' // TournamentTeam表中匹配的主键/关联字段，根据实际表结构调整
});

MatchModel.belongsTo(TournamentTeamModel, {
  foreignKey: 'AwayUID', // Match表中存储客队赛事队伍ID的字段
  as: 'AwayTournamentTeam',
  targetKey: 'TournamentTeamUID'
});

TournamentTeamModel 关联 TeamModel #

// TournamentTeamModel.js
TournamentTeamModel.belongsTo(TeamModel, {
  foreignKey: 'TeamUID', // TournamentTeam表中存储基础队伍ID的字段
  as: 'Team', // 关联别名
  targetKey: 'TeamUID' // Team表的主键，根据实际结构调整
});

// 若TournamentTeam表存在FeedTeam独立关联（如FeedTeamUID），可追加以下配置
// TournamentTeamModel.belongsTo(TeamModel, {
//   foreignKey: 'FeedTeamUID',
//   as: 'FeedTeam'
// });

TeamModel 反向关联（可选） #

如果需要从Team查询其参与的赛事队伍及比赛，可添加：

// TeamModel.js
TeamModel.hasMany(TournamentTeamModel, {
  foreignKey: 'TeamUID',
  as: 'TournamentTeams'
});

2. 修正后的查询代码 #

const data = await MatchModel.findAll({
  attributes: [
    'MatchUID', 'TournamentID', 'ScheduleDate',
    literal('DATE_SUB(ScheduleDate, INTERVAL MatchModel.TimeDelay SECOND) AS ScheduleDateOriginal'),
    'HomeUID', 'AwayUID',
    literal('IFNULL(HomeTournamentTeam.Team.Team, HomeTournamentTeam.Team.FeedTeam) AS HomeTeam'),
    literal('IFNULL(AwayTournamentTeam.Team.Team, AwayTournamentTeam.Team.FeedTeam) AS AwayTeam'),
    literal("IFNULL(HomeTournamentTeam.Team.TeamLogo, '') AS HomeLogo"),
    literal("IFNULL(AwayTournamentTeam.Team.TeamLogo, '') AS AwayLogo"),
    literal('IF(IFNULL(JSON_LENGTH(LineupOut), 0) > 0, 1, 0) AS LineupOut'),
    'DelayMinute',
    literal("IFNULL(MatchModel.DelayMessage, '') AS DelayMessage"),
    [literal('HomeTournamentTeam.Team.FeedTeamName'), 'HomeTeamFullName'],
    [literal('AwayTournamentTeam.Team.FeedTeamName'), 'AwayTeamFullName'],
    'ScoreCard', 'TimeDelay', 'TimeDelayStatus', 'ShowMegaLable', 'IsInLeagueLeaderboard', 'MaxPrize', 'MaxPrizeGameName'
  ],
  include: [
    {
      model: TournamentTeamModel,
      required: true,
      as: 'HomeTournamentTeam',
      include: [
        {
          model: TeamModel,
          required: true,
          as: 'Team'
        }
      ]
    },
    {
      model: TournamentTeamModel,
      required: true,
      as: 'AwayTournamentTeam',
      include: [
        {
          model: TeamModel,
          required: true,
          as: 'Team'
        }
      ]
    }
  ]
});
return resolve({ success: true, data });

注意事项 #

• 所有foreignKey、targetKey需严格匹配你的数据库表结构，若Match的HomeUID直接关联TournamentTeam的TeamUID而非主键，需同步修改targetKey。
• 若TournamentTeam表的Team、FeedTeam是直接存储名称的字段而非关联ID，需去掉嵌套的Team关联，直接引用HomeTournamentTeam.Team这类字段。
• 查询中避免使用未定义的表别名（如原代码的M、T），改用关联配置的as别名确保引用准确。

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