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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
