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

Question

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

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

Answer 1

简单一点的版本:

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