1

I have two tables:

HotelInfo:

    class HotelInfo(db.Model):
    __tablename__ = 'hotel_info'
    country_area = db.Column(db.String(45), nullable=False, primary_key=True)
    hotel_id = db.Column(db.String(32), unique=True, nullable=False, primary_key=True)
    hotel_name = db.Column(db.Text)
    hotel_url = db.Column(db.Text)
    hotel_address = db.Column(db.Text)
    review_score = db.Column(db.Float, nullable=True)
    review_qty = db.Column(db.Integer, nullable=True)
    clean = db.Column(db.Float, nullable=True)
    comf = db.Column(db.Float, nullable=True)
    loct = db.Column(db.Float, nullable=True)
    fclt = db.Column(db.Float, nullable=True)
    staff = db.Column(db.Float, nullable=True)
    vfm = db.Column(db.Float, nullable=True)
    wifi = db.Column(db.Float, nullable=True)
    reviews = db.relationship('HotelReview', backref='hotel_info', lazy=True)

And HotelReview:

class HotelReview(db.Model):
    __tablename__ = 'hotel_reviews'
    uuid = (db.Column(db.String, nullable=True, primary_key=True))
    hotel_id = db.Column(db.String, db.ForeignKey('hotel_info.hotel_id'))
    review_title = db.Column(db.Text)
    review_url = db.Column(db.Text)
    review_score = db.Column(db.Float, nullable=True)
    review_date = db.Column(db.DateTime)
    reviewer_name = db.Column(db.Text)
    hash_reviewer_name = db.Column(db.String, nullable=True)
    reviewer_location = db.Column(db.String, nullable=True)
    posting_conts = db.Column(db.Integer, nullable=True)
    positive_content = db.Column(db.Text)
    negative_content = db.Column(db.Text)
    tag_n1 = db.Column(db.Text)
    tag_n2 = db.Column(db.Text)
    tag_n3 = db.Column(db.Text)
    tag_n4 = db.Column(db.Text)
    tag_n5 = db.Column(db.Text)
    staydate = db.Column(db.Text)

I'm trying to get hotels with more than 5 reviews, and the reviews from those hotels between 2 dates.

I have this for the moment:

This is for getting the hotels with more than 5 reviews

Hotels = HotelInfo.query.filter(HotelInfo.review_qty >= 5)

I know with that I can get the all the reviews from an specific hotel with hotel[0].reviews for example, but I want keep only the most recent reviews.

I have this for get only the most recent reviews:

reviews = HotelReview.review_date.between('2018-01-01', '2019-12-31')

But don't have idea to do all together with one query.

AdamWist
  • 318
  • 2
  • 13
  • Why do you want to do it in a single query? I don't think it's even possible. Think about how it might look in a SQL language – gribvirus74 Mar 10 '21 at 16:56
  • @grbvirus74 yes i think its possible...it will be somthing like that in sql ```SELECT * FROM hotel_info INNER JOIN hotel_reviews ON hotel_reviews.hotel_id = hotel_info.hotel_id WHERE hotel_reviews.review_date BETWEEN ('2018-01-01') AND ('2019-12-012')``` Is'nt correct? – AdamWist Mar 10 '21 at 17:03
  • @AdamWist but then you have only `HotelInfo` without reviews – gribvirus74 Mar 10 '21 at 17:07
  • But Inner Join dont put the tables together?? – AdamWist Mar 10 '21 at 17:10
  • @Nishant If OP uses `Hotel.reviews` then SQLAlchemy will make another call to the database to fetch the reviews. – gribvirus74 Mar 10 '21 at 17:10
  • no it doesn't. It just links those two tables by their keys. – gribvirus74 Mar 10 '21 at 17:12
  • @gribvirus74 So...how can i do it?? – AdamWist Mar 10 '21 at 17:14
  • That's the catch - you can't. You need two queries for that – gribvirus74 Mar 10 '21 at 17:18
  • You can always use a `JOIN `in SQlALchemy right? https://stackoverflow.com/questions/6044309/sqlalchemy-how-to-join-several-tables-by-one-query. I don't know if you can do this in a clean `ORM` like syntax, worth thinking! – Nishant Mar 10 '21 at 17:24
  • Seems already answered here https://stackoverflow.com/questions/51862572/filtering-after-a-join-in-flask-sqlalchemy/51863204 – Jonathan Hamel Mar 10 '21 at 18:12

0 Answers0