mysql - 如何在sqlalchemy中进行此查询

SELECT * FROM city WHERE id in
  (SELECT distinct(id) FROM city c WHERE id in
     (SELECT city_id from address WHERE id in (SELECT address_id FROM maintener)));

我试图只获取在我的维护者地址模型中有参考的城市。也就是说，如果我的城市表中有200个城市，但维护者表中仅引用了40个城市，则我只需要在城市过滤器中显示40个城市。

我在坚持sqlalchemy中有这个模型

class Maintener(persist.Base):
    __tablename__ = 'maintener'

        id = Column(Integer, primary_key=True)
        name = Column(String(255))
        address_id = Column(ForeignKey(u'address.id'), index=True)
        address = relationship(u'Address', lazy='noload', \
  primaryjoin='Maintener.address_id == Address.id')


class Address(persist.Base):
    __tablename__ = 'address'

        id = Column(Integer, primary_key=True)
        state_id = Column(ForeignKey(u'state.id'), index=True)
        city_id = Column(ForeignKey(u'city.id'), index=True)
        state = relationship(u'State', primaryjoin='Address.state_id == State.id', lazy='noload')
        city = relationship(u'City', primaryjoin='Address.city_id == City.id', lazy='noload')

class City(persist.Base):
     __tablename__ = 'city'

         id = Column(Integer, primary_key=True, server_default=FetchedValue(), autoincrement=False)
         name = Column(String(200))
         state_id = Column(ForeignKey(u'state.id'), index=True)
         state = relationship(u'State',
  primaryjoin='City.state_id == State.id', \
  backref=backref(u'cities', lazy='noload'), lazy='noload')

如果有人可以帮助我，我将不胜感激

最佳答案

较简单的版本：

SELECT *
FROM city
WHERE id in (
  SELECT a.city_id
  FROM address a
    JOIN maintener m on m.address_id=a.id
)