Declarative Filters#
Use FilterSet to group allowed query fields,
sorting and relationship loading for one mapped model.
Example Setup#
from sqlalchemy import Column, ForeignKey, Table
from sqlalchemy.orm import Mapped, mapped_column, relationship
from advanced_alchemy.base import BigIntAuditBase
from advanced_alchemy.repository import SQLAlchemyAsyncRepository
filterset_post_tag = Table(
"filterset_post_tag", BigIntAuditBase.metadata,
Column("post_id", ForeignKey("filterset_post.id"), primary_key=True),
Column("tag_id", ForeignKey("filterset_tag.id"), primary_key=True),
)
class FilterSetTag(BigIntAuditBase):
__tablename__ = "filterset_tag"
slug: Mapped[str]
class FilterSetPost(BigIntAuditBase):
__tablename__ = "filterset_post"
title: Mapped[str]
published: Mapped[bool] = mapped_column(default=False)
published_at: Mapped[int] = mapped_column(default=0)
tags: Mapped[list[FilterSetTag]] = relationship(secondary=filterset_post_tag)
class FilterSetPostRepository(SQLAlchemyAsyncRepository[FilterSetPost]):
pass
Reusable FilterSets#
Use FilterSet to keep allowed query fields,
search, sorting, and pagination together. Pass an instance directly to a repository
or service read method:
from advanced_alchemy.filters import FilterSet
class PostFilters(FilterSet[FilterSetPost]):
filter_fields = {
"title": {"contains", "eq"},
"published": {"eq"},
"tags.slug": {"eq", "in"},
}
sort_fields = {"title", "published_at"}
search_fields = {"title", "tags.slug"}
config = {"pagination_type": "limit_offset", "pagination_size": 20, "pagination_max_size": 100}
async def find_filtered_posts(db_session: AsyncSession) -> list[FilterSetPost]:
repository = FilterSetPostRepository(session=db_session)
filters = PostFilters(title__contains="python", tags__slug__eq="sqlalchemy")
return await repository.get_many(filters)
The generic argument supplies the mapped model; model_type can also be set
explicitly. FilterSet construction takes typed Python values. Framework providers
validate and convert HTTP query values. Pydantic remains optional. Set
pagination_max_size (default None) in config or on the class to cap
pageSize/page_size; oversized values raise ValueError when instantiating
a FilterSet and HTTP 400/422 through Litestar or FastAPI. When pagination_size
is omitted, the default page size is clamped to pagination_max_size, and
pagination_size > pagination_max_size raises ImproperConfigurationError.
Set "pagination_type": "cursor" to accept a cursor value instead of page;
see Cursor Pagination.
Field parameters use double underscores to separate relationship names, the field,
and the operator: ?tags__slug__eq=sqlalchemy&title__contains=python. A deeper
path such as orders__items__price__ge=10 follows the corresponding configured
orders.items.price path. Only declared fields and operators are accepted.
Existing FilterConfig dictionaries keep their own parameter aliases and behavior.
Loading Relationships#
Passing a FilterSet directly to a read method also loads relationships referenced
by active field filters, search, and sorting. To-one paths use joined eager loading;
collections use select-in loading. SQL Server collections whose parent has a
composite primary key use subquery loading with deterministic pagination ordering.
Explicit load= options take precedence on overlapping paths.
Loaded collections retain all members allowed by existing loader criteria;
filtering a post by a matching tag does not restrict its loaded tags to that tag.
Relationships used only by response serialization still need explicit load=
options. Set auto_load = False on the FilterSet class to disable inferred loading.
For explicit integration, filters.to_statement_filters() returns the ordinary
filter objects and filters.load returns the inferred loading options. Expanding
only the filter list omits the automatic-loading metadata:
async def find_posts_with_explicit_filters(db_session: AsyncSession) -> list[FilterSetPost]:
repository = FilterSetPostRepository(session=db_session)
filters = PostFilters(tags__slug__eq="sqlalchemy")
return await repository.get_many(*filters.to_statement_filters(), load=filters.load)
DuckDB can raise an internal database error when UUID-correlated EXISTS filters
are combined with a window-function count. For that query shape, call
get_many_and_count(filters, count_with_window_function=False) to use separate
data and count queries.
Custom Filter Methods#
Add a typed filter_<name> method for application-specific filtering. The method
returns an existing filter object, a supported SQLAlchemy predicate, or None
to omit the condition:
class PublishedPostFilters(PostFilters):
def filter_only_published(self, value: bool) -> ComparisonFilter:
return ComparisonFilter(field_name="published", operator="eq", value=value)
only_published = PublishedPostFilters(only_published=True)
The custom method’s argument annotation supplies the query value type. Custom SQL predicates must be supported by the in-memory evaluator when using mock repositories.
Framework Providers#
Pass the FilterSet class to the existing providers; it supplies its own model:
from advanced_alchemy.extensions.fastapi.providers import provide_filters
from advanced_alchemy.extensions.litestar.providers import create_service_dependencies
from advanced_alchemy.service import SQLAlchemyAsyncRepositoryService
class FilterSetPostService(SQLAlchemyAsyncRepositoryService[FilterSetPost]):
pass
post_dependencies = create_service_dependencies(FilterSetPostService, "posts", filters=PostFilters)
post_filter_dependency = provide_filters(PostFilters)
FastAPI’s dependency yields the usual filter list; pass it with *filters to a
repository or service. Litestar service bundles expose the usual aggregate filter
dependency. For a ServiceGroup, configure each member independently with
filters={"posts": PostFilters}. Generated field parameters use the FilterSet’s
double-underscore names in both frameworks’ OpenAPI schemas.