Using SQLAlchemy ORM I would like to add a calculated column and then sort by that column. The calculated column is the max value of two other columns (I'm trying to find the most recent interaction between 2 contacts and they can interact either through 'email' or 'notes').
I've constructed a case when statement that correctly returns the most recent interaction:
calculatedLastInteraction = case([
(
(organization_note.time == None) & (contact.last_interaction_time_email != None),
contact.last_interaction_time_email
),
(
(contact.last_interaction_time_email == None) & (organization_note.time != None),
organization_note.time
),
(
contact.last_interaction_time_email >= organization_note.time,
contact.last_interaction_time_email
),
(
organization_note.time > contact.last_interaction_time_email,
organization_note.time
)
],
else_ = None).label('calculatedLastInteractionDate')
How do I add this column to an existing SQLAlchemy query?