Using sqlalchemy, how do i set up a filter that is comparing the value of one column to the value of a function run on multiple columns.
The goal is to write an orm query that selects objects where dt_added + max_age seconds < now.
If I were to just write the raw sql, this is the statement I'd be going for:
SELECT * FROM sometable s WHERE DATE_ADD(s.dt_added,interval max_age second) < NOW()
The solutions I looked at suggest using raw python datetime objects, but that won't work here because different rows have different values for max_age. The math has to happen on the server side.
class SomeObject(Base): __tablename__ = 'sometable' some_id = Column(Integer, primary_key=True) modified = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) max_age = Column(Integer)
id, dt_added, max_age 1, 2015-12-30 12:15:00, 3600 2, 2015-12-30 12:15:00, 60
If the current datetime was '2015-12-30 12:20:00' it would return row 2, but not row 1.
I am able to force the issue using raw text, but I'd rather do it completely through the orm.
from sqlalchemy.sql import func from sqlalchemy.sql.expression import text query = ses.query( SomeObject ).filter( func.date_add( SomeObject.created, text('INTERVAL max_age second') ) < datetime.datetime.now()
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `sometable` ADD INDEX `sometable_idx_date_age` (`date_add_dt_added_interval_max_age`);
SELECT * FROM sometable s WHERE s.date_add_dt_added_interval_max_age < NOW()