sqlalchemy子查询参与比较筛选

今天项目中遇到一个用子查询的字段进行筛选的查询，先放Model

class HomeKeep_staff(Base):
    '''
    家政服务员工
    '''
    __tablename__ = 'homeKeep_staff'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    sex = Column(Integer, nullable=False, default=0)
    photo = Column(Integer, default=config.DEFAULT_PHOTO)
    birth = Column(Integer, nullable=False)
    homeKeep_id = Column(Integer, ForeignKey('homeKeep.id'), nullable=False)
    homeKeep = relationship('HomeKeep', backref='staffs')
    introduce = Column(String)
    diploma = Column(String)

class HomeKeep_type(Base):
    '''
    家政服务类别
    '''
    __tablename__ = 'homeKeep_type'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    unit = Column(String, nullable=False)
    homeKeep_staffs = relationship('HomeKeep_staff_homeKeep_type')

class HomeKeep_staff_homeKeep_type(Base):
    '''
    员工技能价格
    '''
    __tablename__ = 'homeKeep_staff_homeKeep_type'
    id = Column(Integer, primary_key=True)
    homeKeep_staff_id = Column(Integer, ForeignKey('homeKeep_staff.id'), nullable=False)
    homeKeep_staff = relationship('HomeKeep_staff', backref='homeKeep_staff_homeKeep_types')
    homeKeep_type_id = Column(Integer, ForeignKey('homeKeep_type.id'), nullable=False)
    homeKeep_type = relationship('HomeKeep_type', backref='homeKeep_staff_homeKeep_types')
    price = Column(Float, nullable=False, default=0.0)



class HomeKeep(Base):
    '''
    家政服务中心
    '''
    __tablename__ = 'homeKeep'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, default='未命名的家政服务中心')
    introduce = Column(String)
    shopKeeper_id = Column(Integer, ForeignKey('shopKeeper.id'), nullable=False)
    ShopKeeper = relationship('ShopKeeper', backref=backref("homeKeep", uselist=False))
    range_dis = Column(Integer)
    lng = Column(FLOAT)
    lat = Column(FLOAT)
    address = Column(String)
    is_sell = Column(Integer, nullable=False, default=0)


    area_id = Column(Integer, ForeignKey('config_area.id'), nullable=False)
    area = relationship('Config_area', backref='homeKeeps')

以上为所用到的四个表的结构

现在所需要查询的是根据给定的HomeKeep(家政中心)的id数组，和一个(HomeKeep_staff)员工的id，还有对应的技能（HomeKeep_type）id，求出在给定家政中心列表内工作的，拥有同一技能且价格在原先员工价格上下浮动1000以内的价格

首先查出原先员工的价格的query

origin_price = self.orm.query(HomeKeep_staff_homeKeep_type.price).filter(
            HomeKeep_staff_homeKeep_type.homeKeep_staff_id == HomeKeep_staff.id,
            HomeKeep_staff_homeKeep_type.homeKeep_type_id == originOrder.homeKeep_type_id)

还有现在员工价格的query

 type_staff_price_query = self.orm.query(HomeKeep_staff_homeKeep_type.price).filter(
            HomeKeep_staff_homeKeep_type.homeKeep_staff_id == HomeKeep_staff.id,
            HomeKeep_staff_homeKeep_type.homeKeep_type_id == originOrder.homeKeep_type_id)

下面再基于这两个个query写完整的查询

一开始我是这么写的

staffs = self.orm.query(HomeKeep_staff).filter(HomeKeep_staff.id != originOrder.homeKeep_staff_id,
                                                       HomeKeep_staff.homeKeep_id.in_(
                                                           [homeKeep.id for homeKeep in homeKeeps])).filter(
            type_staff_price_query.exists()
        ).filter(
            type_staff_price_query <= origin_price_query+ 1000,
            type_staff_price_query > origin_price_query - 1000

        )

发现报了两张种错

1. TypeError: unsupported operand type(s) for +: 'Query' and 'int'
2. 查询出来怎么都没结果

第一个问题我把origin_price_query改为了origin_price_query.scalar()，也就是直接查询出结果再参与查询

出现了第二个问题

然后我print(type_staff_price_query<=100000)

得到的结果竟然是

false

而不是想象中的sql语句，我猜测query对象没有重载那些操作符魔法方法，所以不支持直接的加减和比较，

后来改为了

staffs = self.orm.query(HomeKeep_staff).filter(HomeKeep_staff.id != originOrder.homeKeep_staff_id,
                                                       HomeKeep_staff.homeKeep_id.in_(
                                                           [homeKeep.id for homeKeep in homeKeeps])).filter(
            type_staff_price_query.exists()
        ).filter(
            type_staff_price_query.label('type_staff_price_query1') <= originPrice.label('origin_price') + 1000,
            type_staff_price_query.label('type_staff_price_query1') >= originPrice.label('origin_price') - 1000

        )

用一个label函数，label返回的对象重载了操作符，可以进行比较和运算

下次做就注意下子查询作为字段最好用label重命名一下