SQLAlchemy联接来自同一表的多个列

菲菲爷爷

class Match(Base):
tablename = ‘matches’
    id = Column(Integer, primary_key=True)
* i( k" z+ \& }; x8 T    date = Column(Date, nullable=False)
% B$ j6 J$ ~8 q( H, |2 d" O    time = Column(Time, nullable=True)
    league_id = Column(ForeignKey('leagues.id'), nullable=False, index=True)
. ]  v, O- o8 Z5 x  |+ X    league = relationship('League', backref='matches')
    type = Column(enums.matches_types)
    home_team_id = Column(ForeignKey('teams.id'), nullable=False, index=True)
" f( K3 u& U% e4 E5 H" u    home_team = relationship('Team', foreign_keys=[home_team_id], backref='home_matches')
  b# F, t; ^7 ]& q2 D    away_team_id = Column(ForeignKey('teams.id'), nullable=False, index=True)
. N  g$ Y  t- w; P8 G& u+ e
class Team(Base):
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    country_id = Column(ForeignKey('countries.id'), nullable=False, index=True)
& G# R9 f/ |& [. Z1 t# B1 g* |    country = relationship('Country', backref='teams')
我需要编写一个查询，该查询将列和团队表连接在一起，以显示本地和客队的团队信息。
Session.query(Match.date, Match.home_team.name, Match_away_team.name).joins(Team)
. q- m3 ]% `6 h9 X$ C( J2 _  y) ~这返回 Can't determine join between 'matches' and 'teams'; tables have more than
one foreign key constraint relationship between them. Please specify the
: `+ ]7 V, W& M( x$ d" C4 m'onclause' of this join explicitly
               
解决方案:
  j; f; v7 f7 a1 J6 F               
8 _' X3 P7 d0 G# B. G/ r

' n- d) C# E: |# [                首先，您的代码不起作用的原因是因为SQLAlchemy不知道您是否要Team通过viahome_team或加入away_team，因此您必须告诉它。此外，您需要加入Team两次，这使事情变得更加复杂。
: `; ~7 g5 X4 w  s5 w( v使用joinedload以下命令可以更轻松地完成此操作：
matches = session.query(Match).options(joinedload(Match.home_team),
                                       joinedload(Match.away_team))
6 r" _  H$ `$ A# h# g& u( q( m* F- {( Gfor m in matches:
    print m.date, m.home_team, m.away_team
+ J1 S7 w* n) e$ k2 ]& O3 J9 Am.home_team并且m.away_team将在相同的查询被装载m使用JOIN。
2 L2 v* w3 Y  Z0 G& u: F" v, y如果你坚持使用一个明确的.join()，你得别名的Team实体（未测试）：
home = aliased(Team)
' |+ |% K* _, ^+ R! n+ ?away = aliased(Team)
q = session.query(Match.date, home, away).join(home, Match.home_team) \
8 A* b. n$ V6 l8 C* j0 j; N: Q                                         .join(away, Match.away_team)
for date, home_team, away_team in q:
+ I- s* Z! R3 m4 D% E  D: R    print date, home_team, away_team