我正在尝试构建一个查询，以过滤酒店价格列表。我只想显示从今天到未来的酒店预订。我建立的查询在下面，但不过滤结果。我究竟做错了什么？
hotels = db.session.query(Hotel).\
join(Location).\
join(Rate).\
filter(Location.city == city).\
filter(Rate.arrive > datetime.utcnow())
对于背景，我的模型如下所示：
class Location(Base):
__tablename__ = 'locations'
id = Column(Integer, primary_key=True)
city = Column(String(50), nullable=False, unique=True)
hotels = relationship('Hotel', back_populates='location')
class Hotel(Base):
__tablename__ = 'hotels'
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False, unique=True)
phone_number = Column(String(20))
parking_fee = Column(String(10))
location_id = Column(Integer, ForeignKey('locations.id'), nullable=False)
location = relationship('Location', back_populates='hotels')
rates = relationship('Rate', back_populates='hotel', order_by='Rate.arrive')
class Rate(Base):
__tablename__ = 'rates'
id = Column(Integer, primary_key=True)
price = Column(Numeric(6, 2))
arrive = Column(Date, nullable=False)
link = Column(String(500), nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
hotel_id = Column(Integer, ForeignKey('hotels.id'), nullable=False)
hotel = relationship('Hotel', back_populates='rates')
编辑：这是一些示例数据：
Date: Friday, Sep 9
Rate: 299.25
Date: Sunday, Sep 11
Rate: 261.75
Date: Monday, Sep 12
Rate: 261.75
Date: Tuesday, Sep 13
Rate: 261.75
Date: Sunday, Sep 18
删除过滤器（Rate.arrive> datetime.utcnow（））不会更改数据。每次我运行查询时，输出都相同。
选项零用于在加载酒店后应用费率过滤器，但这将生成其他查询，因此这是不希望的。
定义稍后将使用的过滤器：
city = 'mumbai'
arrive = datetime.date.today()
选项1：Rate初始查询中的目标负载
query = (
session.query(Hotel, Rate) # this query will return pairs (Hotel, Rate)
.join(Location)
.join(Rate)
.filter(Location.city == city)
.filter(Rate.arrive > arrive)
)
# one could use the results directly as (Hotel, Rate) pairs
# but we can also convert to the format: {hotel: rates}
from collections import defaultdict
hotels_list = defaultdict(list)
for h, r in hotels:
hotels_list[h].append(r)
# and print:
for hotel, rates in hotels_list.items():
print('', hotel)
for rate in rates:
print(' ', rate)
选项2：欺骗关系Hotel.rates
在这里我们使用contains_eager
hotels = (
session.query(Hotel)
.join(Location)
.join(Rate)
.options(contains_eager(Hotel.rates)) # this is the key
.filter(Location.city == city)
.filter(Rate.arrive > arrive)
)
for hotel in hotels:
print('', hotel)
for rate in hotel.rates: # this now contains only sub-list, so be careful
print(' ', rate)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句