SQL炼金术联接来自同一表的多个列 [英] SQL Alchemy Join Multiple Columns from same table
问题描述
class Match(Base):
__tablename__ = 'matches'
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
time = Column(Time, nullable=True)
league_id = Column(ForeignKey('leagues.id'), nullable=False, index=True)
league = relationship('League', backref='matches')
type = Column(enums.matches_types)
home_team_id = Column(ForeignKey('teams.id'), nullable=False, index=True)
home_team = relationship('Team', foreign_keys=[home_team_id], backref='home_matches')
away_team_id = Column(ForeignKey('teams.id'), nullable=False, index=True)
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)
country = relationship('Country', backref='teams')
我需要编写一个查询,该查询将列和队表连接起来,以显示本地队和客队的队信息.
I need to write a query that joins columns and teams tables displaying information of the teams information for both local and away team.
Session.query(Match.date, Match.home_team.name, Match_away_team.name).joins(Team)
这将返回Can't determine join between 'matches' and 'teams'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly
推荐答案
首先,您的代码不起作用的原因是因为SQLAlchemy不知道您是否要通过home_team
或Team
,这会使事情变得更加复杂.
First, the reason your code doesn't work is because SQLAlchemy doesn't know whether you want to join to Team
via home_team
or away_team
, so you'll have to tell it. In addition, you'll need to join to Team
twice, which further complicates things.
matches = session.query(Match).options(joinedload(Match.home_team),
joinedload(Match.away_team))
for m in matches:
print m.date, m.home_team, m.away_team
m.home_team
和m.away_team
将使用JOIN
在与m
相同的查询中加载.
m.home_team
and m.away_team
will be loaded in the same query as m
using a JOIN
.
如果您坚持使用显式的.join()
,则必须别名 Team
实体(未测试):
If you insist on using an explicit .join()
you'll have to alias the Team
entities (not tested):
home = aliased(Team)
away = aliased(Team)
q = session.query(Match.date, home, away).join(home, Match.home_team) \
.join(away, Match.away_team)
for date, home_team, away_team in q:
print date, home_team, away_team
这篇关于SQL炼金术联接来自同一表的多个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!