CODEWITHSUNDEEP
pythonsqlalchemy
Khakis7
Khakis7

Reputation: 433

sqlalchemy only apply filter if value is not None

I'm working on creating a sqlalchemy filter that looks for rows between a specific time-range, but I only want this filter to apply if they have a value for the day. If they don't not, they should also be returned. The filter looks like this:

 models = {
        CheckinAction: Student.person_id == CheckinAction.person_id,
        CheckinTimeRange: CheckinAction.chk_checkin_time_range_id == CheckinTimeRange.pk,
    }

    return join_models(query, models, "outerjoin").filter(
        CheckinTimeRange.day >= pendulum.parse(start_day).date(), CheckinTimeRange.day <= pendulum.parse(end_day).date()
    )

In the above code, the CheckinAction and CheckinTimeRange models are outerjoined onto an existing Student query. I'd like to only apply the CheckinTimeRange.day filter if they have a checkin action. An example would be:

row #1: {
student_id = 1,
student_name = 'row_to_not_filter',
checkin_action = None,
checkin_time_range = None
}

row #2: {
student_id = 2,
student_name = 'row_to_filter',
checkin_action = some_object,
checkin_time_range = {day = '2020-04-21'}
}

I'd like to only apply the filter to the second row because they have a checkin_time_range.

Upvotes: 0

Views: 861

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52929

Since you are using a LEFT JOIN, move the predicate to the ON clause of the join:

from sqlalchemy import and_

...
    models = {
        CheckinAction: Student.person_id == CheckinAction.person_id,
        CheckinTimeRange: and_(
            CheckinAction.chk_checkin_time_range_id == CheckinTimeRange.pk,
            CheckinTimeRange.day >= pendulum.parse(start_day).date(),
            CheckinTimeRange.day <= pendulum.parse(end_day).date()
        )
    }

    return join_models(query, models, "outerjoin")

Because CheckinAction.chk_checkin_time_range_id is used in the ON clause of the join to CheckinTimeRange, the AND will evaluate to NULL for row 1 and so achieves the result of not including the time range, if the action is missing. Moving the range predicate to the ON clause also achieves that rows with no range at all are included in the results.

Upvotes: 1

Related Questions