I have a table `special_product_list` and a table `special_product_historical_details_list` that holds information of products over time, where there's a record in the history table every day for every product.
I want to write a query to select all products from `special_product_list` that meet the two following conditions: - the product's most recent `special_product_historical_details_list` entry has `available=true` - at least one other `special_product_historical_details_list` entry for that product except the most recent one has `available=false` if the following were the most recent `special_product_historical_details_list` entries for a product, only in the first situation would I want that product to be returned from the query p_id | date | is_available ---------------------------- 1 | 2017-12-13 | true 1 | 2017-12-12 | false 1 | 2017-12-11 | true 1 | 2017-12-10 | true p_id | date | is_available ------------------------------- 1 |2017-12-13 | false 1 |2017-12-12 | true 1 |2017-12-11 | false 1 |2017-12-10 | false p_id | date | is_available ------------------------------- 1 |2017-12-13 | true 1 |2017-12-12 | true 1 |2017-12-11 | true 1 |2017-12-10 | true Is there a way I could write this in SqlAlchemy? Here are my models: class SpecialProduct(Product): __tablename__ = 'special_product_list' special_id = db.Column(String(64), nullable=False, index=True, primary_key=True) history = relationship("SpecialProductHistoricalDetails", back_populates='special_product', foreign_keys='SpecialProductHistoricalDetails.special_id') most_recent_historical_details_id = db.Column(Integer, ForeignKey('special_product_historical_details_list.id')) most_recent_historical_details_entry = relationship("SpecialProductHistoricalDetails", uselist=False, foreign_keys=[most_recent_historical_details_id]) class SpecialProductReviewsHistoricalDetails(db.Model): __tablename__ = 'special_product_historical_details_list' id = db.Column(Integer, primary_key=True) special_id = db.Column(String(64), ForeignKey('special_product_list.special_id'), nullable=False) special_product = relationship("SpecialProduct", uselist=False, foreign_keys=[special_id]) time_updated = db.Column(TIMESTAMP, default=func.now(), onupdate=func.now(), server_default=text("CURRENT_TIMESTAMP")) Thanks! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.