CODEWITHSUNDEEP
pythonsqlalchemy
Casey
Casey

Reputation: 2709

Sqlalchemy filter by date

I'm trying to construct a query that filters a list of hotel rates. I only want to show hotel reservations from today and into the future. The query I built is below but does not filter the results. What am I doing wrong?

hotels = db.session.query(Hotel).\
    join(Location).\
    join(Rate).\
    filter(Location.city == city).\
    filter(Rate.arrive > datetime.utcnow())

For background, my models look like this:

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

Edit: Here is some sample data:

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

Removing filter(Rate.arrive > datetime.utcnow()) does not change the data. It is output the same each time I run the query.

Upvotes: 0

Views: 2214

Answers (1)

van
van

Reputation: 76962

Option zero is to apply the filter of rates after you loaded the hotels, but this will generate additional queries, and is not desired.

Define filters which are used later on:

city = 'mumbai'
arrive = datetime.date.today()

Option-1: Load Rate of interest in the initial query

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)

Option-2: Trick the relationship Hotel.rates

Here we are using 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)

Upvotes: 1

Related Questions