Metadata-Version: 2.4
Name: filtrate
Version: 1.0.0
Summary: Build reusable, composable filters for declarative SQLAlchemy models
Keywords: typed,database,orm,sqlalchemy,filtering,filters
Author: Elias Gabriel
Author-email: Elias Gabriel <oss@eliasfgabriel.com>
License-Expression: BSD-3-Clause-Clear
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: Natural Language :: English
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Utilities
Classifier: Typing :: Typed
Requires-Dist: beartype>=0.23.0rc0,<1
Requires-Dist: sqlalchemy>=2,<2.1
Requires-Python: >=3.14
Project-URL: Homepage, https://github.com/thearchitector/filtrate
Project-URL: Repository, https://github.com/thearchitector/filtrate.git
Project-URL: Issues, https://github.com/thearchitector/filtrate/issues
Description-Content-Type: text/markdown

<!-- pragma: no ai -->

# filtrate

![PyPI Downloads](https://img.shields.io/pypi/dm/filtrate?style=flat)
[![bear-ified](https://raw.githubusercontent.com/beartype/beartype-assets/main/badge/bear-ified.svg)](https://beartype.readthedocs.io)
![GitHub Actions Workflow Status](https://img.shields.io/github/actions/workflow/status/thearchitector/filtrate/ci.yaml?style=flat)

Build reusable, composable filters for declarative SQLAlchemy models.

It supports:

- building filters with AND, OR, and negation at any depth
- filtering ORM properties
    - mapped columns
    - hybrid proprties
    - relationships
    - association proxies
- dynamic properties backed by custom SQL scalar expressions
- a bunch of built-in operators and predicates, such as `Contains`, `OneOf`, `Exists` etc.
- a capability system to keep Predicates reusable and type-safe, and support filtering on custom ORM types

Requires Python 3.14 and SQLAlchemy `>=2,<2.1`. Type checked.

## Installation

```bash
python -m pip install filtrate
# or
uv add filtrate
```

## Quick start

Add `FilterableMixin` to a declarative model, construct a `Filter`, and use `as_filtered_by()` anywhere SQLAlchemy accepts a where clause:

<!-- test: quick-start -->

```python
from sqlalchemy import select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from filtrate import Equals, Filter, FilterableMixin, Match


class Base(DeclarativeBase):
    pass


class BasicUser(FilterableMixin, Base):
    __tablename__ = "basic_user"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]
    age: Mapped[int]


filter_ = Filter(match=Match(property="age", using=Equals(42)))
statement = select(BasicUser).where(BasicUser.as_filtered_by(filter_))
```

Filters are frozen and hashable, making them safe to reuse as cache keys or compare as values.

### Built-in predicates

Predicates describe an operation applied to a property:

| Family     | Predicates                                                                    |
| ---------- | ----------------------------------------------------------------------------- |
| Comparison | `Equals`, `OneOf`                                                             |
| Ordering   | `LessThan`, `LessThanOrEqual`, `GreaterThan`, `GreaterThanOrEqual`, `Between` |
| Text       | `Contains`, `StartsWith`, `EndsWith` and their `Exact` variants               |
| Presence   | `Exists`                                                                      |

Text Predicates escape `%` and `_`. The default variants are case-insensitive; `Exact` variants are case-sensitive.

## Composing filters

Filters compose with `and_`, `or_`, and `negate`:

<!-- test: building-filters -->

```python
from filtrate import Contains, GreaterThan, OneOf

filter_ = Filter(
    and_=(
        Filter(match=Match(property="age", using=GreaterThan(17))),
        Filter(
            or_=(
                Filter(match=Match(property="email", using=Contains("@example"))),
                Filter(match=Match(property="id", using=OneOf((1, 2, 3)))),
            )
        ),
    )
)
```

Filters, at any level, can be negated:

<!-- test: negated-filter -->

```python
not_42 = Filter(match=Match(property="age", using=Equals(42)), negate=True)
```

## Filter relationships

You can filter models by their relationships using `Related`. Its entire inner filter will apply to the same related row:

<!-- test: filter-relationship -->

```python
from filtrate import Related

filter_ = Filter(
    via=Related(
        relationship="orders",
        where=Filter(
            and_=(
                Filter(match=Match(property="status", using=Equals("open"))),
                Filter(match=Match(property="total", using=GreaterThan(100))),
            )
        ),
    )
)
```

### Association proxies

Direct column-targeted association proxies work like ordinary properties:

<!-- test: association-proxy -->

```python
from sqlalchemy import ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import Mapped, mapped_column, relationship
from filtrate import Contains, Filter, FilterableMixin, Match


class Foo(Base):
    __tablename__ = "foo"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Bar(FilterableMixin, Base):
    __tablename__ = "bar"

    id: Mapped[int] = mapped_column(primary_key=True)
    foo_id: Mapped[int] = mapped_column(ForeignKey("foo.id"))
    foo: Mapped[Foo] = relationship()

    foo_name = association_proxy("foo", "name")


filter_ = Filter(match=Match(property="foo_name", using=Contains("python")))
```

### Dynamic properties

A property resolver allows you to define filterable properties that are not ordinary model attributes. Simply override `get_property()`
on your `FilterableMixin` subclass and return a custom `FilterProperty`.

<!-- test: dynamic-property -->

```python
from sqlalchemy import func, String
from filtrate import Contains, Filter, Match, FilterProperty


class User(FilterableMixin, Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]

    @classmethod
    def get_property(cls, property: str) -> FilterProperty:
        if property == "email_domain":
            domain = func.substr(
                cls.email, func.instr(cls.email, "@") + 1, type_=String
            )
            return FilterProperty(name=property, orm_attr=domain, is_relationship=False)

        return super().get_property(property)


filter_ = Filter(match=Match(property="email_domain", using=Contains("example")))
clause = User.as_filtered_by(filter_)
```

## Advanced usage

### Custom predicates

You can implement custom Predicates to expose more filtering logic in your applications.

Just subclass `Predicate` (or `Operator` if your custom logic takes operands) and implement `apply`:

<!-- test: custom-predicate -->

```python
from filtrate import FilterClause, Operator, Property, register


# you can also use @dataclass, but this decorator will do that for you AND enable runtime type checking!
@register
class IsDivisibleBy(Operator[int]):
    operand: int

    def apply(self, property: Property[int]) -> FilterClause:
        return property % self.operand == 0
```

### Custom type capabilities

A capability is a type's declaration that it supports a particular kind of Predicate.

A type is responsible for making an operation work, including any special handling it requires, so the Predicate can remain general and reusable.

Text Predicates require `Capability.TEXTUAL`, and ordering Predicates require `Capability.ORDERED`.
Equality, membership, and presence Predicates are unrestricted.

Custom SQLAlchemy types must declare the operations they support:

<!-- test: custom-type -->

```python
from sqlalchemy import String
from sqlalchemy.types import TypeDecorator
from filtrate import Capability, filter_capabilities


@filter_capabilities(Capability.TEXTUAL)
class CaseFoldedText(TypeDecorator[str]):
    impl = String
    cache_ok = True
```

### Direct usage

While the recommended usage pattern is inheriting `FilterableMixin`, filtrate actually supports direct usage with _any_ declarative model.

Simply import `compile_filter` directly and call it with your model and Filter. It also allows passing a custom property factory.

When used directly, keep in mind that a few things are different:

- the property factory _will be shared by every model traversed in your filter tree_, including relationships.
- to fallback to library property resolution, return `None`.

<!-- test: direct-usage -->

```py
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from filtrate import FilterProperty, compile_filter


def dynamic(model: type[DeclarativeBase], property: str) -> FilterProperty | None:
    if model is Foo:
        if property == "last_name":
            domain = func.substr(Foo.name, func.instr(Foo.name, ".") + 1, type_=String)
            return FilterProperty(name=property, orm_attr=domain, is_relationship=False)

    return None


filter_ = Filter(match=Match(property="last_name", using=Contains("wonka")))
clause = compile_filter(Foo, filter_, dynamic)
```

## License

BSD 3-Clause Clear.
