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?
Advertisement
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?