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.

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: Optional[datetime]

Filter results where field is earlier than this value.

after: Optional[datetime]

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:

TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])

__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: Optional[Collection[TypeVar(T)]]

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:

TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])

__init__(field_name, values)

class advanced_alchemy.filters.ComparisonFilter

Bases: StatementFilter

Simple comparison filter for equality and inequality operations.

This filter applies basic comparison operators (=, !=, >, >=, <, <=) to a field. It provides a generic way to perform common comparison operations.

field_name: str

Name of the model attribute to filter on.

operator: str

Comparison operator to use (one of ‘eq’, ‘ne’, ‘gt’, ‘ge’, ‘lt’, ‘le’).

value: Any

Value to compare against.

append_to_statement(statement, model)

Apply a comparison operation to the statement.

Parameters:

• statement (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – The SQLAlchemy model class

Returns:

Modified statement with the comparison condition

Return type:

StatementTypeT

__init__(field_name, operator, value)

class advanced_alchemy.filters.ExistsFilter

Bases: StatementFilter

Filter for EXISTS subqueries.

This filter creates an EXISTS condition using a list of column expressions. The expressions can be combined using either AND or OR logic. The filter applies a correlated subquery that returns only the rows from the main query that match the specified conditions.

For example, if searching movies with Movie.genre == “Action”, only rows where the genre is “Action” will be returned.

Parameters:

• values (list[ColumnElement[bool]]) – values: List of SQLAlchemy column expressions to use in the EXISTS clause

• operator (Literal["and", "or"], optional) – operator: If “and”, combines conditions with AND, otherwise uses OR. Defaults to “and”.

• Example

• -------- –

  Basic usage with AND conditions:

  from sqlalchemy import select
  from advanced_alchemy.filters import ExistsFilter
  
  filter = ExistsFilter(
      values=[User.email.like("%@example.com%")],
  )
  statement = filter.append_to_statement(
      select(Organization), Organization
  )
  

  This will return only organizations where the user’s email contains “@example.com”.

  Using OR conditions:

  filter = ExistsFilter(
      values=[User.role == "admin", User.role == "owner"],
      operator="or",
  )
  

  This will return organizations where the user’s role is either “admin” OR “owner”.

• Also (See)

• -------- – NotExistsFilter: The inverse of this filter sqlalchemy.sql.expression.exists(): SQLAlchemy EXISTS expression

values: list[ColumnElement[bool]]

List of SQLAlchemy column expressions to use in the EXISTS clause.

operator: Literal['and', 'or'] = 'and'

If “and”, combines conditions with the AND operator, otherwise uses OR.

__init__(values, operator='and')

get_exists_clause(model)

Generate the EXISTS clause for the statement.

Parameters:

model (type[TypeVar(ModelT, bound= ModelProtocol)]) – type[ModelT] The SQLAlchemy model class to correlate with

Returns:

A correlated EXISTS expression for use in a WHERE clause

Return type:

ColumnElement[bool]

append_to_statement(statement, model)

Append EXISTS condition to the statement.

Parameters:

• statement (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – StatementTypeT The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – type[ModelT]

• class (The _sphinx_paramlinks_advanced_alchemy.filters.ExistsFilter.append_to_statement.SQLAlchemy model)

Returns:

Modified statement with EXISTS condition

Return type:

StatementTypeT

class advanced_alchemy.filters.FilterGroup

Bases: StatementFilter

A group of filters combined with a logical operator.

This class combines multiple filters with a logical operator (AND/OR). It provides a way to create complex nested filter conditions.

__init__(logical_operator, filters)

logical_operator: Callable[..., ColumnElement[bool]]

Logical operator to combine the filters.

filters: list[StatementFilter]

List of filters to combine.

append_to_statement(statement, model)

Apply all filters combined with the logical operator.

Parameters:

• statement (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – The SQLAlchemy model class

Returns:

Modified statement with combined filters

Return type:

StatementTypeT

class advanced_alchemy.filters.FilterMap

Bases: TypedDict

before_after: type[BeforeAfter]

on_before_after: type[OnBeforeAfter]

collection: type[CollectionFilter[Any]]

not_in_collection: type[NotInCollectionFilter[Any]]

limit_offset: type[LimitOffset]

order_by: type[OrderBy]

search: type[SearchFilter]

not_in_search: type[NotInSearchFilter]

comparison: type[ComparisonFilter]

exists: type[ExistsFilter]

not_exists: type[NotExistsFilter]

filter_group: type[FilterGroup]

advanced_alchemy.filters.FilterTypes: TypeAlias = 'Union[BeforeAfter, OnBeforeAfter, CollectionFilter[Any], LimitOffset, OrderBy, SearchFilter, NotInCollectionFilter[Any], NotInSearchFilter, ExistsFilter, NotExistsFilter, ComparisonFilter, MultiFilter, FilterGroup]'

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 (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – 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.LogicalOperatorMap

Bases: TypedDict

and_: Callable[..., ColumnElement[bool]]

or_: Callable[..., ColumnElement[bool]]

class advanced_alchemy.filters.MultiFilter

Bases: StatementFilter

Apply multiple filters to a query based on a JSON/dict input.

This filter provides a way to construct complex filter trees from a structured dictionary input, supporting nested logical groups and various filter types.

__init__(filters)

filters: dict[str, Any]

JSON/dict structure representing the filters.

append_to_statement(statement, model)

Apply the filters to the statement based on the filter definitions.

Parameters:

• statement (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – The SQLAlchemy model class

Returns:

Modified statement with all filters applied

Return type:

StatementTypeT

class advanced_alchemy.filters.NotExistsFilter

Bases: StatementFilter

Filter for NOT EXISTS subqueries.

This filter creates a NOT EXISTS condition using a list of column expressions. The expressions can be combined using either AND or OR logic. The filter applies a correlated subquery that returns only the rows from the main query that DO NOT match the specified conditions.

For example, if searching movies with Movie.genre == “Action”, only rows where the genre is NOT “Action” will be returned.

Parameters:

• values (list[ColumnElement[bool]]) – values: List of SQLAlchemy column expressions to use in the NOT EXISTS clause

• operator (Literal["and", "or"], optional) – operator: If “and”, combines conditions with AND, otherwise uses OR. Defaults to “and”.

• Example

• -------- –

  Basic usage with AND conditions:

  from sqlalchemy import select
  from advanced_alchemy.filters import NotExistsFilter
  
  filter = NotExistsFilter(
      values=[User.email.like("%@example.com%")],
  )
  statement = filter.append_to_statement(
      select(Organization), Organization
  )
  

  This will return only organizations where the user’s email does NOT contain “@example.com”.

  Using OR conditions:

  filter = NotExistsFilter(
      values=[User.role == "admin", User.role == "owner"],
      operator="or",
  )
  

  This will return organizations where the user’s role is NEITHER “admin” NOR “owner”.

• Also (See)

• -------- – ExistsFilter: The inverse of this filter sqlalchemy.sql.expression.not_(): SQLAlchemy NOT operator sqlalchemy.sql.expression.exists(): SQLAlchemy EXISTS expression

__init__(values, operator='and')

values: list[ColumnElement[bool]]

List of SQLAlchemy column expressions to use in the NOT EXISTS clause.

operator: Literal['and', 'or'] = 'and'

If “and”, combines conditions with the AND operator, otherwise uses OR.

get_exists_clause(model)

Generate the NOT EXISTS clause for the statement.

Parameters:

model (type[TypeVar(ModelT, bound= ModelProtocol)]) – type[ModelT] The SQLAlchemy model class to correlate with

Returns:

A correlated NOT EXISTS expression for use in a WHERE clause

Return type:

ColumnElement[bool]

append_to_statement(statement, model)

Append NOT EXISTS condition to the statement.

Parameters:

• statement (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – StatementTypeT The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – type[ModelT] The SQLAlchemy model class

Returns:

Modified statement with NOT EXISTS condition

Return type:

StatementTypeT

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: Optional[Collection[TypeVar(T)]]

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:

TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])

__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 (Union[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 (Optional[bool]) – 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: Optional[datetime]

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

on_or_after: Optional[datetime]

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:

TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])

__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.

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 (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – 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

__init__(field_name, sort_order='asc')

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

field_name: Union[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: Optional[bool] = 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[TypeVar(ModelT, bound= ModelProtocol)]) – 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 (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – The SQLAlchemy model class

Returns:

Modified statement with text search clauses

Return type:

StatementTypeT

See also

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

__init__(field_name, value, ignore_case=False)

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.

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

Append filter conditions to a SQLAlchemy statement.

Parameters:

• statement (TypeVar(StatementTypeT, bound= Union[ReturningDelete, ReturningUpdate, Select, Update, Delete])) – The SQLAlchemy statement to modify

• model (type[TypeVar(ModelT, bound= ModelProtocol)]) – 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