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)
Sample data:
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()
Many thanks.
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()