Trying to use SQLAlchemy relationships

As an example, I have a cities table and a movie theaters table. A city has multiple movie theaters so:

class City(Base):
    __tablename__ = 'cities'
    id = Column(Integer, primary_key=True)
    name = Column(VARCHAR(255))
    theaters = relationship("Theater", backref=backref("theaters"))

class Theater(Base):
    __tablename__ = 'theaters'
    id = Column(Integer, primary_key=True)
    city_id = Column(Integer, ForeignKey('cities.id'))
    name = Column(VARCHAR(255), nullable=False)

now i want to get all theaters for a city:

theaters = db_session.query(City).filter_by(city = "New York").join(Theater.city_id).all()

This query throws error:

sqlalchemy.exc.ArgumentError: Join target Theater.city_id does not refer to a mapped entity

not sure what I’m doing wrong?

Answer

Something like this should automatically do the join for you:

class City(Base):
    __tablename__ = 'cities'
    id = Column(Integer, primary_key=True)
    name = Column(VARCHAR(255))
    theaters = relationship(
        "Theater", 
        lazy='joined', 
        backref=backref("city")  # This is how you refer to the city
                                 # from theater. In other words, this adds
                                 # a field "city" to the Theater class.
    )

class Theater(Base):
    __tablename__ = 'theaters'
    id = Column(Integer, primary_key=True)
    city_id = Column(Integer, ForeignKey('cities.id'))
    name = Column(VARCHAR(255), nullable=False)

And then the query would be

ny_theaters = session.query(Theater).filter(Theater.city.has(City.name == 'New York')).all()

Make sense?