How to accommodate None types when comparing dates in SQL Alchemy Query?

Question

My query:

abandoned_days = 10
abandoned_date = datetime.now() - timedelta(days=abandoned_days)

abandoned_requests = db.query(models.DocumentRequest)\
    .filter(
        models.DocumentRequest.case_id.in_([case["id"] for case in cases]),
        models.DocumentRequest.status == 'requested',
        models.DocumentRequest.requested_at < abandoned_date) \
    .all()

My Error: TypeError: '<' not supported between instances of 'NoneType' and 'datetime.datetime'

abandoned_date will always be a datetime type, however how can I make this database query to not query a record when it's requested_at value is none (or in SQL, null). But also still request records using the models.DocumentRequest.requested_at < abandoned_date line?

Answer 1

There are many options how to achieve this.

*option 1: add filter to exclude such rows

    .filter(
...
        models.DocumentRequest.requested_at != None,  # new filter
        models.DocumentRequest.requested_at < abandoned_date,
    ).all()

option 2: convert those to the date which will fail the filter

    .filter(
...
        sqlalchemy.func.coalesce(models.DocumentRequest.requested_at, datetime.now()) < abandoned_date,
    ).all()