filters

SQLAlchemy filter constructs for advanced query operations.

This module provides a comprehensive collection of filter datastructures designed to enhance SQLAlchemy query construction. It implements type-safe, reusable filter patterns for common database query operations.

Features:

Type-safe filter construction, datetime range filtering, collection-based filtering, pagination support, search operations, and customizable ordering.

Example

Basic usage with a datetime filter:

import datetime
from advanced_alchemy.filters import BeforeAfter

filter = BeforeAfter(
    field_name="created_at",
    before=datetime.datetime.now(),
    after=datetime.datetime(2023, 1, 1),
)
statement = filter.append_to_statement(select(Model), Model)

Note

All filter classes implement the StatementFilter ABC, ensuring consistent interface across different filter types.

See also

• sqlalchemy.sql.expression.Select: Core SQLAlchemy select expression

• sqlalchemy.orm.Query: SQLAlchemy ORM query interface

• advanced_alchemy.base: Base model definitions

class advanced_alchemy.filters.BeforeAfter

Bases: StatementFilter

DateTime range filter with exclusive bounds.

This filter creates date/time range conditions using < and > operators, excluding the boundary values.

If either before or after is None, that boundary condition is not applied.

See Also:

OnBeforeAfter : Inclusive datetime range filtering

field_name: str

Name of the model attribute to filter on.

before: datetime | None

Filter results where field is earlier than this value.

after: datetime | None

Filter results where field is later than this value.

append_to_statement(statement, model)

Apply datetime range conditions to statement.

Parameters:

• statement (StatementTypeT) – The SQLAlchemy statement to modify

• model (type[ModelT]) – The SQLAlchemy model class

• Returns

• --------

• StatementTypeT – Modified statement with datetime range conditions

Return type:

advanced_alchemy.filters.StatementTypeT

__init__(field_name, before, after)

class advanced_alchemy.filters.CollectionFilter

Bases: InAnyFilter, Generic[T]

Data required to construct a WHERE … IN (…) clause.

This filter restricts records based on a field’s presence in a collection of values.

The filter supports both IN and ANY operators for collection membership testing. Use prefer_any=True in append_to_statement to use the ANY operator.

field_name: str

Name of the model attribute to filter on.

values: Collection[TypeVar(T)] | None

Values for the IN clause. If this is None, no filter is applied. An empty list will force an empty result set (WHERE 1=-1)

append_to_statement(statement, model, prefer_any=False)

Apply a WHERE … IN or WHERE … ANY (…) clause to the statement.

Parameters:

• statement (StatementTypeT) – The SQLAlchemy statement to modify

• model (type[ModelT]) – The SQLAlchemy model class

• prefer_any (bool, optional) – If True, uses the SQLAlchemy any_() operator instead of in_() for the filter condition

• Returns

• --------

• StatementTypeT – Modified statement with the appropriate IN conditions

Return type:

advanced_alchemy.filters.StatementTypeT

__init__(field_name, values)

advanced_alchemy.filters.FilterTypes: TypeAlias = 'BeforeAfter | OnBeforeAfter | CollectionFilter[Any] | LimitOffset | OrderBy | SearchFilter | NotInCollectionFilter[Any] | NotInSearchFilter'

Aggregate type alias of the types supported for collection filtering.

class advanced_alchemy.filters.InAnyFilter

Bases: StatementFilter, ABC

Base class for filters using IN or ANY operators.

This abstract class provides common functionality for filters that check membership in a collection using either the SQL IN operator or the ANY operator.

class advanced_alchemy.filters.LimitOffset

Bases: PaginationFilter

Limit and offset pagination filter.

Implements traditional pagination using SQL LIMIT and OFFSET clauses. Only applies to SELECT statements; other statement types are returned unmodified.

Note

This filter only modifies SELECT statements. For other statement types (UPDATE, DELETE), the statement is returned unchanged.

See also

• sqlalchemy.sql.expression.Select.limit(): SQLAlchemy LIMIT clause

• sqlalchemy.sql.expression.Select.offset(): SQLAlchemy OFFSET clause

limit: int

Maximum number of rows to return.

offset: int

Number of rows to skip before returning results.

append_to_statement(statement, model)

Apply LIMIT/OFFSET pagination to the statement.

Parameters:

• statement (advanced_alchemy.filters.StatementTypeT) – The SQLAlchemy statement to modify

• model (type[advanced_alchemy.repository.typing.ModelT]) – The SQLAlchemy model class

Returns:

Modified statement with limit and offset applied

Return type:

StatementTypeT

Note

Only modifies SELECT statements. Other statement types are returned as-is.

See also

sqlalchemy.sql.expression.Select: SQLAlchemy SELECT statement

__init__(limit, offset)

class advanced_alchemy.filters.NotInCollectionFilter

Bases: InAnyFilter, Generic[T]

Data required to construct a WHERE … NOT IN (…) clause.

This filter restricts records based on a field’s absence in a collection of values.

The filter supports both NOT IN and != ANY operators for collection exclusion. Use prefer_any=True in append_to_statement to use the ANY operator.

Parameters:

• field_name (str) – Name of the model attribute to filter on

• values (abc.Collection[T] | None) – Values for the NOT IN clause. If this is None or empty, the filter is not applied.

field_name: str

Name of the model attribute to filter on.

values: Collection[TypeVar(T)] | None

Values for the NOT IN clause. If None or empty, no filter is applied.

append_to_statement(statement, model, prefer_any=False)

Apply a WHERE … NOT IN or WHERE … != ANY(…) clause to the statement.

Parameters:

• statement (StatementTypeT) – The SQLAlchemy statement to modify

• model (type[ModelT]) – The SQLAlchemy model class

• prefer_any (bool, optional) – If True, uses the SQLAlchemy any_() operator instead of notin_() for the filter condition

• Returns

• --------

• StatementTypeT – Modified statement with the appropriate NOT IN conditions

Return type:

advanced_alchemy.filters.StatementTypeT

__init__(field_name, values)

class advanced_alchemy.filters.NotInSearchFilter

Bases: SearchFilter

Filter for excluding records that match a substring.

Implements negative text search using SQL NOT LIKE or NOT ILIKE operators. Can exclude across multiple fields using AND conditions.

Parameters:

• field_name (str | set[str]) – Name or set of names of model attributes to search on

• value (str) – Text to exclude from the field(s)

• ignore_case (bool | None) – If True, uses NOT ILIKE for case-insensitive matching

Note

Uses AND for multiple fields, meaning records matching any field will be excluded.

See also

• SearchFilter: Opposite filter using LIKE/ILIKE

• sqlalchemy.sql.expression.ColumnOperators.notlike(): NOT LIKE operator

• sqlalchemy.sql.expression.ColumnOperators.notilike(): NOT ILIKE operator

__init__(field_name, value, ignore_case=False)

class advanced_alchemy.filters.OnBeforeAfter

Bases: StatementFilter

DateTime range filter with inclusive bounds.

This filter creates date/time range conditions using <= and >= operators, including the boundary values.

If either on_or_before or on_or_after is None, that boundary condition is not applied.

See Also:

BeforeAfter : Exclusive datetime range filtering

field_name: str

Name of the model attribute to filter on.

on_or_before: datetime | None

Filter results where field is on or earlier than this value.

on_or_after: datetime | None

Filter results where field is on or later than this value.

append_to_statement(statement, model)

Apply inclusive datetime range conditions to statement.

Parameters:

• statement (StatementTypeT) – The SQLAlchemy statement to modify

• model (type[ModelT]) – The SQLAlchemy model class

• Returns

• --------

• StatementTypeT – Modified statement with inclusive datetime range conditions

Return type:

advanced_alchemy.filters.StatementTypeT

__init__(field_name, on_or_before, on_or_after)

class advanced_alchemy.filters.OrderBy

Bases: StatementFilter

Order by a specific field.

Appends an ORDER BY clause to SELECT statements, sorting records by the specified field in ascending or descending order.

Note

This filter only modifies SELECT statements. For other statement types, the statement is returned unchanged.

See also

• sqlalchemy.sql.expression.Select.order_by(): SQLAlchemy ORDER BY clause

• sqlalchemy.sql.expression.ColumnElement.asc(): Ascending order

• sqlalchemy.sql.expression.ColumnElement.desc(): Descending order

field_name: str

Name of the model attribute to sort on.

__init__(field_name, sort_order='asc')

sort_order: Literal['asc', 'desc'] = 'asc'

Sort direction (“asc” or “desc”).

append_to_statement(statement, model)

Append an ORDER BY clause to the statement.

Parameters:

• statement (advanced_alchemy.filters.StatementTypeT) – The SQLAlchemy statement to modify

• model (type[advanced_alchemy.repository.typing.ModelT]) – The SQLAlchemy model class

Returns:

Modified statement with an ORDER BY clause

Return type:

StatementTypeT

Note

Only modifies SELECT statements. Other statement types are returned as-is.

See also

sqlalchemy.sql.expression.Select.order_by(): SQLAlchemy ORDER BY

class advanced_alchemy.filters.PaginationFilter

Bases: StatementFilter, ABC

Abstract base class for pagination filters.

Subclasses should implement pagination logic, such as limit/offset or cursor-based pagination.

class advanced_alchemy.filters.SearchFilter

Bases: StatementFilter

Case-sensitive or case-insensitive substring matching filter.

Implements text search using SQL LIKE or ILIKE operators. Can search across multiple fields using OR conditions.

Note

The search pattern automatically adds wildcards before and after the search value, equivalent to SQL pattern ‘%value%’.

See also

• NotInSearchFilter: Opposite filter using NOT LIKE/ILIKE

• sqlalchemy.sql.expression.ColumnOperators.like(): Case-sensitive LIKE

• sqlalchemy.sql.expression.ColumnOperators.ilike(): Case-insensitive LIKE

__init__(field_name, value, ignore_case=False)

field_name: str | set[str]

Name or set of names of model attributes to search on.

value: str

Text to match within the field(s).

ignore_case: bool | None = False

Whether to use case-insensitive matching.

property normalized_field_names: set[str]

Convert field_name to a set if it’s a single string.

Returns:

Set of field names to be searched

Return type:

set[str]

get_search_clauses(model)

Generate the LIKE/ILIKE clauses for all specified fields.

Parameters:

model (type[advanced_alchemy.repository.typing.ModelT]) – The SQLAlchemy model class

Returns:

List of text matching expressions

Return type:

list[BinaryExpression[bool]]

See also

sqlalchemy.sql.expression.BinaryExpression: SQLAlchemy expression

append_to_statement(statement, model)

Append a LIKE/ILIKE clause to the statement.

Parameters:

• statement (advanced_alchemy.filters.StatementTypeT) – The SQLAlchemy statement to modify

• model (type[advanced_alchemy.repository.typing.ModelT]) – The SQLAlchemy model class

Returns:

Modified statement with text search clauses

Return type:

StatementTypeT

See also

sqlalchemy.sql.expression.Select.where(): SQLAlchemy WHERE clause

class advanced_alchemy.filters.StatementFilter

Bases: ABC

Abstract base class for SQLAlchemy statement filters.

This class defines the interface for all filter types in the system. Each filter implementation must provide a method to append its filtering logic to an existing SQLAlchemy statement.

abstract append_to_statement(statement, model, *args, **kwargs)

Append filter conditions to a SQLAlchemy statement.

Parameters:

• statement (advanced_alchemy.filters.StatementTypeT) – The SQLAlchemy statement to modify

• model (type[advanced_alchemy.repository.typing.ModelT]) – The SQLAlchemy model class

• *args (Any) – Additional positional arguments

• **kwargs (Any) – Additional keyword arguments

Returns:

Modified SQLAlchemy statement with filter conditions applied

Return type:

StatementTypeT

Raises:

NotImplementedError – If the concrete class doesn’t implement this method

Note

This method must be implemented by all concrete filter classes.

See also

sqlalchemy.sql.expression.Select.where(): SQLAlchemy where clause