Query Filters¶

from sqlspec.core import (
    AnyCollectionFilter,
    BeforeAfterFilter,
    BooleanFilter,
    ChoicesFilter,
    ConditionFactory,
    CursorFilter,
    InCollectionFilter,
    LimitOffsetFilter,
    NotInCollectionFilter,
    NotNullFilter,
    NullFilter,
    NullsPlacement,
    OrderByFilter,
    SearchFilter,
    StatementFilter,
    apply_filter,
    canonicalize_filters,
)

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.

limit ¶

limit

offset ¶

offset

append_to_statement ¶

append_to_statement(statement)

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 key.	`None`

Raises:

Type	Description
`ValueError`	Keys, limit, or secret are invalid.
`InvalidCursorError`	The token does not match the requested ordering.

keys ¶

keys

limit ¶

limit

cursor ¶

cursor

backward ¶

backward

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.

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`

field_name ¶

field_name

sort_order ¶

sort_order

nulls ¶

nulls

Return the requested NULL placement, or None for the database default.

append_to_statement ¶

append_to_statement(statement)

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.

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.

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 & Condition Factory¶

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.

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

ConditionFactory ¶

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