How to configure relationships between three tables?

Question

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?

Answer 1

Understandably, you want to utilize the relationship mapping of the ORM instead of writing explicit SQL (or mapped queries) outside of your class. To accomplish this, you need to be explicit about primary and secondary joins.

Here's a solution using explicit primary and secondary joins:
Note: The previous answer is both sufficient and correct, my solution is a different style of writing code, but the underlying behavior of the ORM will be nearly equal.

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

Answer 2

Option-1: plain query

Actually, it is very similar to writing a plain SQL query:

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

---

Option-2: computed property After the clarification that you would like to have a "relationship", I actually think that instead of the relationship from Match to Weather it looks to be more efficient to use column_property instead:

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

This will query the weather_conditions whenever you query for Match as a property.

---

FINALLY: Option-3: actual relationship

Define the relationship as shown below (without needing to change any other part of the model definitions):

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

The Configuring how Relationship Joins link you put in your question contains examples of similar solutions.

Now, to perform the query you also need to specify the join condition to be able to use the query you want though:

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