Nested Filters

SQLAlchemy filters plus provides a way to build very complex queries by using NestedFilter which make use of existing filter classes to act as Field with the ability to specify how the inner fields of that NestedFilter should be grouped and specifying how to combine it with the other declared fields using AndOperator and OrOperator.

Let’s create an complete example:

from sqlalchemy.fields import StringField, IntegerField, MethodField
from sqlalchemy.filters import DateField, Filter, NestedFilter
from sqlalchemy.operators import GTOperator, LTEOperator, ContainsOperator, AndOperator, OrOperator

class MyFilter(Filter):
    min_age = IntegerField(field_name="age", lookup_operator=GTOperator)
    max_age = IntegerField(field_name="age", lookup_operator=LTEOperator)
    created_at = MethodField(method="filter_created_at")

    def filter_created_at(self, value):
        return User.created_at == value

    class Meta:
        model = User
        fields = ["first_name", "min_age", "max_age", "created_at"]

class SecondFilter(Filter):
    email = StringField(lookup_operator=ContainsOperator)
    max_birth_date = DateField(field_name="birth_date", lookup_operator=LTEOperator)
    nested_data = NestedFilter(
        MyFilter,
        operator=AndOperator,  # How to join the inner fields of MyFilter (first_name, min_age)
        outer_operator=OrOperator,  # How to join the result of the NestedFilter with the
                                    # rest of SecondFilter's fields
        data_source_name="nested_data_key",  # Used to specify from where the data should be extracted
                                             # ignored if flat is True
        flat=False  # True if MyFilter fields should be extracted at the root level,
                    # If False, then the fields data will be extracted from the key data_source_name if specified
                    # otherwise from the NestedFilter field name, in our example it's `nested_data`
    )

    class Meta:
        model = User
        session = my_session

Let’s filter some objects

my_filter = SecondFilter(data={
    "email": "@example",
    "max_birth_date": "1980-01-01",
    "nested_data": {
        "first_name": "John",
        "min_age": 25,
        "max_age": 45,
        "created_at": "2020-01-01",
    }
}, operator=OrOperator)
users = my_filter.apply_all()

The result of the sql query would be similar to

SELECT users.id,
       users.first_name,
       users.last_name,
       users.email,
       users.age,
       users.is_approved,
       users.birth_date,
       users.created_at,
       users.last_login_time
FROM   users
WHERE  ( users.created_at = '2020-01-01'
          OR users.age > 25
          OR users.age <= 45
          OR users.first_name = 'John' )
       AND ( (users.email LIKE '%' || '@example' || '%') OR users.birth_date <= '1980-01-01' )

Note

NestedFilter can use other NestedFilters as fields.