Object filtering strategy in Indico

indico-obj-filtering.md

This document discusses the common strategy of Indico for filtering objects from the DB. We can either go:

• Implicit
• Explicit

Implicit

Ferhat came up with a helper+decorator to standardize the way we filter objects:

@staticmethod
@utils.filtered
def filterReservations(**filters):
    return Reservation, Reservation.query

Reservation.filterReservations(
    room_id=65,
    start_date=('ge', start_date),
    end_date=('lt', end_date),
    created_at=('gt', today))

Explicit

If we opt out Ferhat's approach we would need to do something like this:

@staticmethod
def filterReservations(**filters):
    """
    min_date: Reservations taking place after date
    max_date: Reservations taking place before date
    created_before: Reservations created before date
    created_after: Reservations created after date
    ...
    """

    min_date = filters.pop('min_date')
    max_date = filters.pop('max_date')
    created_before = filter.pop('created_before)
    created_after = filter.pop('created_after)
    
    q = Reservation.query
    
    if min_date is not None:
        q.filter(Reservation.start_date >= min_date)
    if max_date is not None:
        q.filter(Reservation.end_date <= max_date)
    if created_before is not None:
        q.filter(Reservation.created_at < created_before)
    if created_after is not None:
        q.filter(Reservation.created_at > created_after)
    
    q.filter_by(**filters)
    return return.all()

Reservation.filterReservations(
    room_id=65,
    min_date=start_date,
    max_date=end_date,
    created_before=today)

Pros and cons

Ferhat's approach requires this code in our system:

def apply_filters(q, entity, **filters):
    """
    for a given start query `q`, given filters are applied onto query

    query mapping:
        eq   => ==
        ne   => !=
        ge   => >=
        gt   => >
        lt   => <
        le   => <=
        like => like
        in   => in_

        `%` must be explicitly given for `like` filter
    """

    for column_name, condition in filters.items():
        column = getattr(entity, column_name)

        # assume equal unless operator is specified
        if isinstance(condition, tuple):
            requested_operator, value = condition
        else:
            requested_operator, value = 'eq', condition

        try:
            mapped_operator = filter(
                lambda possible_attr: hasattr(
                    column,
                    possible_attr % requested_operator
                ),
                ['%s', '%s_', '__%s__']
            )[0] % requested_operator
        except IndexError:
            raise RuntimeError('Invalid filter operator')

        q = q.filter(getattr(column, mapped_operator)(value))
    return q


def filtered(func):
    """
    apply filters and returns all objects
    """
    assert re.match(r'filter\w+s', func.__name__)
    def add_filters(*args, **filters):
        cls, q = func(*args, **filters)
        return apply_filters(q, cls, **filters).all()
    return add_filters