Filtering functions¶

class metapensiero.sqlalchemy.proxy.filters.Filter¶

Represent a single filter condition.

classmethod make(property, value, operator=<Operator.EQUAL>)¶

Helper to create a new instance.

The operator gets coerced to an Operator instance using its Operator.make() class method.

class metapensiero.sqlalchemy.proxy.filters.Operator(operator, filter_factory)¶

Some kind of comparison operator between a field and a value.

BETWEEN = ('><', <function _op_between>)¶

The field value must be within a given range of values.

The range may be a tuple of two values, a dictionary with either a start or end key or both.

CONTAINS = ('~', <function _op_contains>)¶

The field value must contain the given value.

For string columns it uses ilike() with a pattern %value%, otherwise it uses contains() and the exact semantic is determined by the data type.

EQUAL = ('=', <function _op_equal>)¶

The field value must match the given value.

GREATER = ('>', <function Operator.<lambda>>)¶

The field value must be greater than the specified value.

GREATER_OR_EQUAL = ('>=', <function Operator.<lambda>>)¶

The field value must be equal or greater than the specified value.

LESSER = ('<', <function Operator.<lambda>>)¶

The field value must be less than the specified value.

LESSER_OR_EQUAL = ('<=', <function Operator.<lambda>>)¶

The field value must be equal or less than the specified value.

NOT_EQUAL = ('<>', <function _op_not_equal>)¶

The field value must be different than the specified value.

STARTSWITH = ('~=', <function _op_startswith>)¶

The field value must start with the given value.

For string columns it uses ilike() with a pattern value%, otherwise it uses startswith() and the exact semantic is determined by the data type.

filter(column, value)¶

Return a filter expression that compares column with value.

metapensiero.sqlalchemy.proxy.filters.apply_filters(query, args)¶

Filter a given query.

Parameters:	query – an SQLAlchemy Query args – a dictionary
Return type:	a tuple

query may be either a SQL statement (not necessarily a SELECT) or an ORM query.

The args dictionary may contain some special keys, that will be used to build a filter expression, or to change the query in particular ways.

Important

All these keys will be consumed, that is removed from the args dictionary.

filter_col

the name of the field going to be filtered

filter_value

value of the filter

filter_by_name-of-the-field

specify both the name-of-the-field and the value to apply

filter (or filters)

a sequence of filter specifications, or a JSON string containing a list of dictionaries: each dictionary must contain a property and a value slots and an optional operator which is prepended to the given value, if it already does not when specified

only_cols

filter the selected columns of the query, using only fields specified with this argument, assumed to be a comma separated list of field names

query

this is used combined with fields: if present, its value will be searched in the specified fields, within an OR expression

fields

this is a list of field names that selects which fields will be compared to the query value

The function extract_filters() is used to build the filter expression.

Returns a tuple with the modified query in the first slot, and another which is either None or the list of columns specified by only_cols.

metapensiero.sqlalchemy.proxy.filters.extract_filters(args)¶

Extract filter conditions.

Parameters:	args – a dictionary, usually request.params
Return type:	a list of Filter instances

Recognize three possible syntaxes specifying filtering conditions:

1. the “old” way: ?filter_col=fieldname&filter_value=1
2. the “new” way: a filter or filters argument with a (possibly JSON encoded) array of dictionaries, each containing a property slot with the field name as value, an operator slot and a value slot
3. a custom syntax: ?filter_by_fieldname=1

The different syntaxes may be specified together, and they will be applied in the order above.

Note

the args parameter is modified in place!

metapensiero.sqlalchemy.proxy.filters.split_operator_and_value(value, default_operator=<Operator.EQUAL>)¶

Given a string value, recognize possible prefix comparison operator.

If value is a string that starts with a known operator, split the value returning a tuple like (Operator.XXX, remaining-value); if it contains the >< operator, return (Operator.BETWEEN, (start, end)); otherwise return (Operator.EQUAL, value).