如何配置三个表之间的关系

Question

我目前有以下三个表：

class Match(Base):

id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
tournament_id = Column(Integer, ForeignKey("myschema.tournament.id_"))

class Tournament(Base):
id_ = Column(Integer, primary_key=True)
latitude = Column(Float)
longitude = Column(Float)
match = relationship("Match", backref="tournament")

class Weather(Base):
id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
latitude = Column(Float)
longitude = Column(Float)
conditions = Column(String(50))

我希望构建一个查询，获取Match表中每一场比赛的天气条件。类似这样的东西：

qry = session.query(Match.id_, Weather.conditions)
qry = qry.select_from(Match)
qry = qry.join(Tournament, Weather)
qry = qry.all()

Weather中的唯一密钥是date_time、latitude和longitude的组合，其中：

• date_time需要连接到Match中的等价物(多对多(
• latitude需要连接到Tournament中的等价物(多对多(
• longitude需要连接到Tournament中的等价物(多对多(

我可以处理外键上的简单关系，例如Match和Tournament的关系，但我在试图找出更复杂的关系时迷失了方向。

我希望以上内容不言自明——如果需要数据，请告诉我，我会添加一些。

我使用的是SQLAlchemy v1.3。

---

更新：

我一直在尝试从这里的指南中创建关系：

class Match(Base):
id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
tournament_id = Column(Integer, ForeignKey("myschema.tournament.id_"))
weather = relationship(
"Weather", primaryjoin="Match.date_time == Weather.date_time"
)
class Tournament(Base):
id_ = Column(Integer, primary_key=True)
latitude = Column(Float)
longitude = Column(Float)
match = relationship("Match", backref="tournament")
weather = relationship(
"Weather",
primaryjoin="and_(Tournament.latitude == Weather.latitude, " +
"Tournament.longitude == Weather.longitude)"
)

class Weather(Base):
id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
latitude = Column(Float)
longitude = Column(Float)
conditions = Column(String(50))

然而，当我从上面运行查询时，我得到了错误：

Don't know how to join to <class 'container.Weather'>. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.

我哪里错了？

Answer 1

选项-1：普通查询

实际上，它与编写普通SQL查询非常相似：

qry = (
session.query(Match.id_, Weather.conditions)
.select_from(Match)
.join(Tournament)
.join(
Weather,
and_(
Match.date_time == Weather.date_time,
Tournament.latitude == Weather.latitude,
Tournament.longitude == Weather.longitude,
),
)
).all()

---

选项-2：计算属性在澄清您想要一个"；关系"；，事实上，我认为使用column_property而不是Match to Weather中的relationship看起来更有效：

class Match(Base):
__tablename__ = "match"
id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
tournament_id = Column(Integer, ForeignKey("tournament.id_"))
# NOTE: Weather and Tournament should be defined earlier to use the expressions below. Otherwise, a stringified definition could be used instead
weather_conditions = column_property(
select([Weather.conditions.label("match_weather_conditions")])
.where(tournament_id == Tournament.id_)
.where(date_time == Weather.date_time)
.where(Weather.latitude == Tournament.latitude)
.where(Weather.longitude == Tournament.longitude)
.as_scalar()
.label("weather_conditions")
)

每当您将Match作为属性进行查询时，这将查询weather_conditions。

---

最终：选项-3：实际relationship

定义如下所示的关系(而无需更改模型定义的任何其他部分(：

class Match(Base):
__tablename__ = "match"
id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
tournament_id = Column(Integer, ForeignKey("tournament.id_"))
weather = relationship(
Weather,
secondary=Tournament.__table__,
primaryjoin=tournament_id == Tournament.id_,
secondaryjoin=and_(
Weather.latitude == Tournament.latitude,
Weather.longitude == Tournament.longitude,
date_time == Weather.date_time,
),
viewonly=True,
uselist=False,
)

您在问题中设置的"配置关系联接方式"链接包含类似解决方案的示例

现在，要执行查询，您还需要指定join条件，以便能够使用您想要的查询：

q = session.query(Match.id_, Weather.conditions).join(Weather, Match.weather)

Answer 2

可以理解，您希望利用ORM的关系映射，而不是在类之外编写显式SQL(或映射查询(。要做到这一点，您需要明确主联接和次联接。

这里有一个使用显式主联接和辅助联接的解决方案：
注意：前面的答案既充分又正确，我的解决方案是不同风格的代码编写，但ORM的基本行为几乎相同

from sqlalchemy.ext.associationproxy import association_proxy

class Match(Base):
id_ = Column(Integer, primary_key=True)
date_time = Column(DateTime, index=True)
tournament_id = Column(Integer, ForeignKey("myschema.tournament.id_"))
# Relationship for weather
weather = relationship("Weather",
secondary='Tournament',
primaryjoin='Tournament.id == Match.tournament_id',
secondaryjoin="""
and_(
Tournament.latitude == Weather.latitude,
Tournament.longitude == Weather.longitude,
Match.date_time == Weather.date_time
)
""",
uselist=False,
doc="""Join `Match` and `Weather` on `date_time` while using `Tournament`
as an association table to match `[latitude, longitude]`.
This will allow querying of the weather of a given match but will
not allow updating of weather through a `Match` object.
"""
)
weather_conditions = association_proxy('conditions', 'weather',
doc='Access the weather conditions without unpacking weather relationship')

class Tournament(Base):
id_ = Column(Integer, primary_key=True)
latitude = Column(Float)
longitude = Column(Float)
match = relationship("Match", backref="tournament",
doc='Join on `Tournament.id == Match.tournament_id`')
weather = relationship("Weather",
secondary='Match',
primaryjoin='Tournament.id == Match.tournament_id',
secondaryjoin="""
and_(
Tournament.latitude == Weather.latitude,
Tournament.longitude == Weather.longitude,
Match.date_time == Weather.date_time
)
""",
uselist=False,
doc="""Join `Tournament` and `Weather` on `[latitude, longitude]` while
using `Match` as an association table to match `date_time`.
This will allow querying of the weather of a given tournament but will
not allow updating of weather through a `Tournament` object.
"""
)
weather_conditions = association_proxy('conditions', 'weather',
doc='Access the weather conditions without unpacking weather relationship')