How to configure relationships between three tables?

I currently have the following three tables:

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))

I'm looking to build a query that gets the weather conditions for every match in the Match table. Something like this:

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

The unique key in Weather is the combination of date_time, latitude and longitude where:

• date_time needs to be joined to the equivalent in Match (many to many)
• latitude needs to be joined to the equivalent in Tournament (many to many)
• longitude needs to be joined to the equivalent in Tournament (many to many)

I'm ok with simple relationships on foreign keys, e.g. the one for Match and Tournament, but I get hopelessly lost trying to figure out anything more complex.

I'm hoping the above is self explanatory - if data is needed then please let me know and I'll add some.

I'm using SQLAlchemy v1.3.

---

Update:

I've been experimenting with trying to create the relationships from the guide here:

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))

However, when I run the query from above I get the error:

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.

Where am I going wrong?

from How to configure relationships between three tables?