I have two models for mysql database tables as below:
class Product(Base):
__tablename__ = "products"
id = Column(Integer, primary_key=True, autoincrement=True)
datas = relationship('Data', backref='product')
created_date = Column(Date(), default=date.today())
class Data(Base):
__tablename__ = "data"
id = Column(Integer, primary_key=True, autoincrement=True)
product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
price = Column(Float(2), nullable=False, default=0.0)
revenue = Column(Float(2), nullable=False, default=0.0)
profit = Column(Float(2), nullable=False, default=0.0)
ordered_items = Column(Integer, nullable=False, default=0)
profit_margin = Column(Float(2), nullable=False, default=0.0)
created_date = Column(Date(), default=date.today())
Product table have fixed number of rows but its related rows gets created daily i.e. Data table gets new row for each product everyday.
When I run:
product = session.query(Product).first()
datas = product.datas
I get all the related rows populated in product.datas .
What I want to retrieve is datas in product.datas that are created not older than one month. Above query provides all the related rows and it should be checked in code(python) if its older than a month before proceeding with other calculations. The matter gets worse each day because of new rows being created. This has reduced performance just within 3 months and will become worse.
I delved into sqlalchemy relationship loading technique and more into querying options but could not find a way to do it.
It should be available I think because it uses left outer join to populate related attribute(datas in this case) in background.
Any help would be appreciated. Thank you.