6

This is not a duplicate of this question, because that question is/can be solved using a parameter or constant interval. In my case the interval is defined in a table. My intuition says the following should work:

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import INTERVAL
...
company_uuid = 'some_uuid'
query = db.session.query(CompanyFlagEntity)\
    .join(CompanyFlagTypeEntity)\  # implicit join using fk
    .filter(CompanyFlagEntity.company_uuid == company_uuid)\
    .filter((func.now() - INTERVAL(CompanyFlagTypeEntity.default_lookback_days, 'DAY')) <= CompanyFlagEntity.flag_date)

But I get a the following error:

AttributeError: 'INTERVAL' object has no attribute 'comparator'

The SQL version would be:

select company_flag.*
from company_flag
join company_flag_type on company_flag_type.uuid = company_flag.company_flag_type_uuid
where
    company_flag.company_uuid = 'my_uuid' and 
    (now() - (company_flag_type.default_lookback_days || ' days')::interval) <= flag_date
André C. Andersen
  • 8,955
  • 3
  • 53
  • 79
  • 1
    You could also use `make_interval()`: https://stackoverflow.com/questions/31362484/using-a-sqlalchemy-integer-field-to-create-a-timedelta-object-for-filtering – Ilja Everilä Jul 30 '19 at 09:24
  • @IljaEverilä That is very clever. It works nicely, and is less *hacky* than my solution. If you want credit I'll upvote and accept your answer. If not I can include it in my own answer if you'd like. – André C. Andersen Jul 31 '19 at 07:43
  • I have added an answer to the question you linked that might give you another lead: https://stackoverflow.com/a/76804461/160665 I don't know if it is possible though as I doubt that the dynamic value can be used in the `timedelta` call... which is why I post this as a comment instead of an answer ;) – exhuma Jul 31 '23 at 14:18

1 Answers1

6

The solution fell out of the SQL when I wrote it manually:

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import INTERVAL
from sqlalchemy.sql.functions import concat
...
company_uuid = 'some_uuid'
query = db.session.query(CompanyFlagEntity)\
    .join(CompanyFlagTypeEntity)\  # implicit join using fk
    .filter(CompanyFlagEntity.company_uuid == company_uuid)\
    .filter((func.now() - func.cast(concat(db_base_app.CompanyFlagTypeEntity.default_lookback_days, ' DAYS'), INTERVAL)) <= cls.flag_date)

This referenced helped to find out how to create the interval dynamically in the first place.

André C. Andersen
  • 8,955
  • 3
  • 53
  • 79