在FlaskSQLAlchemy中的3个表之间进行查询、联接和筛选？

Question

我有3个炼金术模型（和一个关联表），具有以下关系：
对决<；->；团队（多对多）
团队->；TeamPoint（一对多）

理想情况下，我希望创建一个query/join语句来查询一个匹配（包含2个团队），并为每个团队返回TeamPoint条目，这些团队与匹配具有相同的周。我已经测试了一些纯SQL查询，通过这些连接，我几乎可以得到正确的结果：

SELECT * FROM matchup m JOIN team_matchup_map tmm ON m.id=tmm.matchup_id # Joining the Matchup side JOIN team t ON tmm.team_id=t.id # Joining the Team side of the association table JOIN team_point tp ON tp.team_key=t.team_key # Joining the TeamPoint table AND m.week=2 AND tp.week=2 AND t.team_id=9 # Filtering on week and team_id

这给了我一个团队id=9的团队，但没有给我在比赛中的另一个团队。在SQLAlchemy中，有没有一种好的方法可以跨多个表进行类似的查询/过滤？目前我在两个查询中进行了这项工作，但它似乎不是最有效的解决方案。以下是我的模型和我的查询路线：

class Team(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    team_id = db.Column(db.Integer, nullable=False)
    team_key = db.Column(db.String(30), nullable=False)
    name = db.Column(db.String(20), unique=True, nullable=False)
    league_id = db.Column(db.Integer, db.ForeignKey('league.league_id'), nullable=False)
    team_stats = db.relationship('TeamStat', backref='team', lazy=True)
    team_points = db.relationship('TeamPoint', backref='team', lazy=True)


class TeamPoint(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    week = db.Column(db.Integer, nullable=True)
    total = db.Column(db.Integer, nullable=True)
    team_key = db.Column(db.String(30), db.ForeignKey('team.team_key'))


class Matchup(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    week = db.Column(db.Integer, nullable=False)
    teams = db.relationship('Team', secondary=team_matchup_map, backref='matchup')
    stat_winners = db.relationship('StatWinner', lazy=True)

team_matchup_map = db.Table('team_matchup_map',
                           db.Column('team_id', db.ForeignKey('team.id'), primary_key=True),
                           db.Column('matchup_id', db.ForeignKey('matchup.id'), primary_key=True),
                           )

这是我当前的路线代码，有两个查询：

@team.route('/matchups/<league_id>/<team_id>', methods=['GET'])
def matchups(league_id=get_user_league().league_id, team_id=get_user_team().team_id):
    week = request.args.get('week')
    # First, querying the Matchup table for the matchup involving the primary team on the given week
    # This provides both teams in the given matchup.
    matchup = Matchup.query.filter(Matchup.teams.any(league_id=league_id, team_id=team_id), Matchup.week == week).first()

    # Next, looping through each team to query the TeamPoint table for the team entry on the same week
    team_points = {}
    for mu_team in matchup.teams:
        if mu_team.team_id == team_id:
            primary_team = mu_team
            team_points[primary_team.team_key] = TeamPoint.query.filter_by(week=week, team_key=mu_team.team_key).first()            
        else:
            opponent = mu_team
            team_points[opponent.team_key] = TeamPoint.query.filter_by(week=week, team_key=mu_team.team_key).first()            

    return render_template('matchups.html', matchup=matchup, primary_team=primary_team, opponent=opponent, team_points=team_points)