Filters

Composable SQL statement filters for building WHERE clauses, pagination, ordering, and search conditions. Filters can be applied to any SQL statement via apply_filter().

apply_filter

sqlspec.core.filters.apply_filter(statement, filter_obj)

Apply a statement filter to a SQL query object.

Parameters:

• statement (SQL) – The SQL query object to modify.

• filter_obj (StatementFilter) – The filter to apply.

Return type:

SQL

Returns:

The modified query object.

Base

class sqlspec.core.filters.StatementFilter

Bases: ABC

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

abstractmethod append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

extract_parameters()

Extract parameters that this filter contributes.

Returns:

• positional_parameters: List of positional parameter values

• named_parameters: Dict of parameter name to value

Return type:

Tuple of (positional_parameters, named_parameters) where

abstractmethod get_cache_key()

Return a cache key for this filter’s configuration.

Return type:

tuple[typing.Any, ...]

Returns:

Tuple of hashable values representing the filter’s configuration

Date Filters

class sqlspec.core.filters.BeforeAfterFilter

Bases: StatementFilter

Filter for datetime range queries.

Applies WHERE clauses for before/after datetime filtering.

__init__(field_name, before=None, after=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Apply filter to SQL expression only.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.OnBeforeAfterFilter

Bases: StatementFilter

Filter for inclusive datetime range queries.

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

__init__(field_name, on_or_before=None, on_or_after=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Collection Filters

class sqlspec.core.filters.InCollectionFilter

Bases: InAnyFilter[T]

Filter for IN clause queries.

Constructs WHERE … IN (…) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotInCollectionFilter

Bases: InAnyFilter[T]

Filter for NOT IN clause queries.

Constructs WHERE … NOT IN (…) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.AnyCollectionFilter

Bases: InAnyFilter[T]

Filter for PostgreSQL-style ANY clause queries.

Constructs WHERE column_name = ANY (array_expression) clauses.

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotAnyCollectionFilter

Bases: InAnyFilter[T]

Filter for PostgreSQL-style NOT ANY clause queries.

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

__init__(field_name, values=None)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Null Filters

class sqlspec.core.filters.NullFilter

Bases: StatementFilter

Filter for IS NULL queries.

Constructs WHERE field_name IS NULL clauses.

__init__(field_name)

extract_parameters()

Extract filter parameters.

Returns empty parameters since IS NULL requires no values.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Apply IS NULL filter to SQL expression.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotNullFilter

Bases: StatementFilter

Filter for IS NOT NULL queries.

Constructs WHERE field_name IS NOT NULL clauses.

__init__(field_name)

extract_parameters()

Extract filter parameters.

Returns empty parameters since IS NOT NULL requires no values.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Apply IS NOT NULL filter to SQL expression.

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Pagination

class sqlspec.core.filters.LimitOffsetFilter

Bases: PaginationFilter

Filter for LIMIT and OFFSET clauses.

Adds pagination support through LIMIT/OFFSET SQL clauses.

__init__(limit, offset)

get_param_names()

Get parameter names without storing them.

Return type:

list[str]

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.OffsetPagination

Bases: Generic[T]

Container for data returned using limit/offset pagination.

__init__(items, limit, offset, total)

Initialize OffsetPagination.

Parameters:

• items (Sequence[TypeVar(T)]) – List of data being sent as part of the response.

• limit (int) – Maximal number of items to send.

• offset (int) – Offset from the beginning of the query. Identical to an index.

• total (int) – Total number of items.

Ordering

class sqlspec.core.filters.OrderByFilter

Bases: StatementFilter

Filter for ORDER BY clauses.

Adds sorting capability to SQL queries.

__init__(field_name, sort_order='asc')

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Search

class sqlspec.core.filters.SearchFilter

Bases: StatementFilter

Filter for text search queries.

Constructs WHERE field_name LIKE ‘%value%’ clauses.

__init__(field_name, value, ignore_case=False)

get_param_name()

Get parameter name without storing it.

Return type:

str | None

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

class sqlspec.core.filters.NotInSearchFilter

Bases: SearchFilter

Filter for negated text search queries.

Constructs WHERE field_name NOT LIKE ‘%value%’ clauses.

get_param_name()

Get parameter name without storing it.

Return type:

str | None

extract_parameters()

Extract filter parameters.

Return type:

tuple[list[typing.Any], dict[str, typing.Any]]

append_to_statement(statement)

Append the filter to the statement.

This method should modify the SQL expression only, not the parameters. Parameters should be provided via extract_parameters().

Return type:

SQL

get_cache_key()

Return cache key for this filter configuration.

Return type:

tuple[typing.Any, ...]

Type Aliases

sqlspec.core.filters.FilterTypes

Union type of all filter classes.