How to limit a query using combination of integer and datetime.date fields

Question

I am trying to build a query that combines 3 tables

set = db(
         (db.Investigation.WorkFlow == db.WorkFlowStatus.WorkFlow)
        &(db.WorkFlow.id == db.Investigation.WorkFlow)
        )

for my last condition I want to select only records that are already overdue with something like this:

&(db.WorkFlowStatus.Overdue < (datetime.date.today() - db.Investigation.ValidFrom))

Field Overdue is integer(representing days), field ValidFrom is datetime.date.

First I am not able to do datetime.date.today() - db.Investigation.ValidFrom only the other way around. If I do db.Investigation.ValidFrom - datetime.date.today() it works but I am still not able to convert my result to integer within the query to be able to compare with my Overdue field.

I tried converting the Overdue field using datetime.timedelta(days=) (unsupported type for timedelta days component: Field) and using .days on the result of my subtraction (Expression object has no attribute days) but nothing seems to work. Is there a way I can add this to my conditions or perhaps a workaround I could use?

Answer 1

You can mix pydal query syntax with sql so with postgres for example:

q = (db.Investigation.WorkFlow == db.WorkFlowStatus.WorkFlow)
q &= (db.WorkFlow.id == db.Investigation.WorkFlow)
q &= "(<WorkFlowStatus.Overdue field_name> < (CURRENT_DATE - <Investigation.ValidFrom field_name>))"
your_set = db(q)

not tested