Skip to content

Query Filters

from sqlspec.core import (
    AnyCollectionFilter,
    BeforeAfterFilter,
    BooleanFilter,
    ChoicesFilter,
    ConditionFactory,
    CursorFilter,
    CursorKey,
    CursorKeys,
    CursorPagination,
    FilterTypes,
    FilterTypeT,
    InCollectionFilter,
    LimitOffsetFilter,
    NotInCollectionFilter,
    NotNullFilter,
    NullFilter,
    NullsPlacement,
    OffsetPagination,
    OrderByFilter,
    Pagination,
    SearchFilter,
    StatementFilter,
    apply_filter,
    canonicalize_filters,
    normalize_cursor_keys,
)
from sqlspec.core.filters import (
    InAnyFilter,
    NotAnyCollectionFilter,
    NotInSearchFilter,
    OnBeforeAfterFilter,
    PaginationFilter,
    find_filter,
)

Composable StatementFilter classes that append WHERE, ORDER BY, LIMIT, OFFSET, and keyset cursor predicates to SQL and Select statements. See Filtering & Pagination for usage examples.

Filter Protocol & Pagination Filters

StatementFilter

Abstract base class for filters that can be appended to a statement.

append_to_statement

append_to_statement(statement)

Append the filter to the statement.

This method modifies the SQL expression and adds parameters via add_named_parameter() on the returned statement.

extract_parameters

extract_parameters()

Return the parameters this filter would contribute.

This is an introspection helper and is not called during the normal filter-application path (append_to_statement handles both expression modification and parameter addition).

Returns:

Type Description
list[Any]

Tuple of (positional_parameters, named_parameters) where:

dict[str, Any]
  • positional_parameters: List of positional parameter values
tuple[list[Any], dict[str, Any]]
  • named_parameters: Dict of parameter name to value

get_cache_key

get_cache_key()

Return a cache key for this filter's configuration.

Returns:

Type Description
tuple[Any, ...]

Tuple of hashable values representing the filter's configuration

LimitOffsetFilter

Bases: PaginationFilter

Filter for LIMIT and OFFSET clauses.

Adds pagination support through LIMIT/OFFSET SQL clauses.

CursorFilter

Bases: PaginationFilter

Paginate by ordered keys whose last key uniquely identifies each row.

Replaces ORDER BY, LIMIT, and OFFSET and fetches limit + 1 rows. Assemble results with build_page() or the service's paginate(). Apply this filter last so other filters constrain the inner query when grouping or set operations require wrapping. paginate() enforces that order automatically.

Parameters:

Name Type Description Default
keys CursorKeys

A column name or sequence of names, (name, direction) tuples, or CursorKey objects, including a unique final tiebreaker. Bare names sort ascending.

required
limit int

Maximum page size.

required
cursor str | None

Optional token identifying the next or previous page.

None
secret str | bytes | None

Optional nonempty signing or encryption key.

None
ttl float | timedelta | None

Optional positive finite cursor lifetime in seconds or timedelta.

None
encrypted bool

Encrypt cursor tokens using HMAC-SHA256 CTR + Encrypt-then-MAC.

False

Raises:

Type Description
ValueError

Keys, limit, secret, or ttl are invalid.

InvalidCursorError

The token does not match the requested ordering, is invalid, or has expired.

encode

encode(row, *, backward)

Mint a token from a selected row.

Parameters:

Name Type Description Default
row Mapping[str, Any]

Result row containing all declared key columns.

required
backward bool

Whether the token traverses backward.

required

Returns:

Type Description
str

A token bound to this filter's ordering and signing key.

Raises:

Type Description
ImproperConfigurationError

A key is missing or unexpectedly NULL.

build_page

build_page(rows)

Trim the sentinel and mint adjacent-page cursors.

Parameters:

Name Type Description Default
rows list[dict[str, Any]]

Raw result rows in effective query order.

required

Returns:

Type Description
CursorPagination[dict[str, Any]]

Items in declared order with page availability and cursor tokens.

append_to_statement

append_to_statement(statement)

Apply cursor predicates, ordering and sentinel over-fetch.

Parameters:

Name Type Description Default
statement SQL

Base query with any earlier filters applied.

required

Returns:

Type Description
SQL

A query with cursor values bound as named parameters.

CursorKey

Declare a sort key and its result-column name.

Parameters:

Name Type Description Default
field_name str

Nonempty SQL column name, optionally qualified.

required
sort_order Literal['asc', 'desc']

Ascending or descending traversal.

'asc'
nulls Literal['first', 'last'] | None

Explicit NULL placement, or None for a non-nullable key.

None
result_name str | None

Selected column name used to extract cursor values.

None

Raises:

Type Description
ValueError

A name, direction, or NULL placement is invalid.

OrderByFilter

Bases: StatementFilter

Filter for ORDER BY clauses.

Adds sorting capability to SQL queries.

Parameters:

Name Type Description Default
field_name str | Expression

Column name or expression to order by.

required
sort_order Literal['asc', 'desc']

Ascending or descending direction.

'asc'
nulls NullsPlacement | None

Explicit NULL placement, or None for the database default.

None

PaginationFilter

Bases: StatementFilter

Base class for pagination-related filters.

CursorKeys

CursorKeys = (
    str | abc.Sequence[CursorKey | str | tuple[str, Literal["asc", "desc"]]]
)

OffsetPagination

Bases: Generic[T]

Container for data returned using limit/offset pagination.

Parameters:

Name Type Description Default
items Sequence[T]

List of data being sent as part of the response.

required
limit int

Maximal number of items to send.

required
offset int

Offset from the beginning of the query. Identical to an index.

required
total int

Total number of items.

required

CursorPagination

Bases: Generic[T]

Container for data returned using cursor (keyset) pagination.

Parameters:

Name Type Description Default
items Sequence[T]

List of data being sent as part of the response.

required
limit int

Maximal number of items to send.

required
next_cursor str | None

Token for the next page, or None on the last page.

required
previous_cursor str | None

Token for the previous page, or None on the first page.

required
has_next bool

Whether a page exists after this one.

required
has_previous bool

Whether a page exists before this one.

required

Pagination

Pagination = CursorPagination[T] | OffsetPagination[T]

NullsPlacement

NullsPlacement = Literal['first', 'last']

Predicate & Collection Filters

BeforeAfterFilter

Bases: _DatetimeBoundFilter

Filter for datetime range queries.

Applies WHERE clauses for before/after datetime filtering.

SearchFilter

Bases: _TextSearchFilter

Filter for text search queries.

Constructs WHERE field_name LIKE '%value%' clauses.

InCollectionFilter

Bases: InAnyFilter[T]

Filter for IN clause queries.

Constructs WHERE ... IN (...) clauses.

OnBeforeAfterFilter

Bases: _DatetimeBoundFilter

Filter for inclusive datetime range queries.

Applies WHERE clauses for on-or-before/on-or-after datetime filtering.

NotInSearchFilter

Bases: SearchFilter

Filter for negated text search queries.

Constructs WHERE field_name NOT LIKE '%value%' clauses.

InAnyFilter

Bases: StatementFilter, Generic[T]

Base class for collection-based filters that support ANY operations.

NotInCollectionFilter

Bases: InAnyFilter[T]

Filter for NOT IN clause queries.

Constructs WHERE ... NOT IN (...) clauses.

AnyCollectionFilter

Bases: InAnyFilter[T]

Filter for PostgreSQL-style ANY clause queries.

Constructs WHERE column_name = ANY (array_expression) clauses.

NotAnyCollectionFilter

Bases: InAnyFilter[T]

Filter for PostgreSQL-style NOT ANY clause queries.

Constructs WHERE NOT (column_name = ANY (array_expression)) clauses.

ChoicesFilter

Bases: InCollectionFilter[T]

Filter for field matching a defined set of choice values.

Constructs WHERE ... IN (...) clauses.

BooleanFilter

Bases: StatementFilter

Filter for boolean comparison queries.

Constructs WHERE field_name = :param clauses.

NullFilter

Bases: StatementFilter

Filter for IS NULL queries.

Constructs WHERE field_name IS NULL clauses.

NotNullFilter

Bases: StatementFilter

Filter for IS NOT NULL queries.

Constructs WHERE field_name IS NOT NULL clauses.

Filter Application & Helpers

FilterTypes

FilterTypes = (
    BeforeAfterFilter
    | OnBeforeAfterFilter
    | InCollectionFilter[Any]
    | CursorFilter
    | LimitOffsetFilter
    | OrderByFilter
    | SearchFilter
    | NotInCollectionFilter[Any]
    | NotInSearchFilter
    | AnyCollectionFilter[Any]
    | NotAnyCollectionFilter[Any]
    | NullFilter
    | NotNullFilter
    | BooleanFilter
    | ChoicesFilter[Any]
)

FilterTypeT

FilterTypeT = TypeVar('FilterTypeT', bound='StatementFilter')

apply_filter

apply_filter(statement, filter_obj)

Apply a statement filter to a SQL query object.

Parameters:

Name Type Description Default
statement SQL

The SQL query object to modify.

required
filter_obj StatementFilter

The filter to apply.

required

Returns:

Type Description
SQL

The modified query object.

find_filter

find_filter(filter_type, filters)

Get the filter specified by filter type from the filters.

Parameters:

Name Type Description Default
filter_type type[FilterTypeT]

The type of filter to find.

required
filters Sequence[StatementFilter | Any]

Filters to search through.

required

Returns:

Type Description
FilterTypeT | None

The match filter instance or None.

canonicalize_filters

canonicalize_filters(filters)

Deduplicate and sort filters by type and field name for consistent hashing.

Parameters:

Name Type Description Default
filters Sequence[StatementFilter]

Sequence of StatementFilter objects

required

Returns:

Type Description
tuple[StatementFilter, ...]

Canonically sorted tuple of unique filters

normalize_cursor_keys

normalize_cursor_keys(keys)

Normalize column names and direction pairs into cursor keys.

Parameters:

Name Type Description Default
keys CursorKeys

A column name or ordered sequence of names, (name, direction) tuples, and explicit cursor keys. Bare names sort ascending.

required

Returns:

Type Description
tuple[CursorKey, ...]

Cursor keys in the supplied order.

Raises:

Type Description
ValueError

A key specification is invalid or the sequence is empty.

ConditionFactory

ConditionFactory = Callable[[exp.Expr, exp.Expr], exp.Expr]