Filter

This class provides filtering functionality on collections with a query string parameter filter. The filter syntax is inspired by MongoDB, made up of predicates containing a property name, an operator, and a value.

Syntax Example: filter=name$eq:Joe - This matches all persons with the value "Joe" in the 'name' property.

Predicates can be chained with logical operators AND, OR, and NOT (to negate a predicate).

Chaining Example: filter=name$eq:Joe$and:city$like:*port$and:country$ne:Norway This matches all persons named Joe, where the city includes 'port' and the country is not Norway.

String filtering is case-insensitive.

Operator affinity can be controlled using parentheses.

Affinity Example: filter=name$eq:Joe$and:$not:(city$like:*port$or:age$lt:40) This matches all persons named Joe where the city does not include 'port' or age is less than 40.

All filter strings should be URL encoded.

The class supports the following filter operators:

• Equals ($eq:)

• Not equals ($ne:)

• Greater than ($gt:)

• Greater than or equal ($gte:)

• Less than ($lt:)

• Less than or equal ($lte:)

• Is NULL ($null:)

• Is NOT NULL ($nnull:)

• Substring match ($like:)

• Logical AND ($and:)

• Logical OR ($or:)

• Logical NOT ($not:)

• In a list ($in:)

• Not in a list ($nin:)

• Having a relational condition ($having:(<relationship-field><operator><value>))

• Having a relational function condition ($having:FUNCTION(<field.subfield>)<operator><value>) where FUNCTION is one of COUNT, SUM, AVG, MIN, MAX

The $having: (without a function) operator is used to filter by a condition on a one-to-many or many-to-many relationship. Example: On the /departments/ endpoint: filter=$having:employees(name$eq:John) - matches all departments with an employee named John.

The $having:FUNCTION operator is used to filter by a conditional function on a one-to-many or many-to-many relationship. Example 1: On the /departments/ endpoint: filter=$having:COUNT(employees)$gt:10 - matches all departments with more than 10 employees. Example 2: On the /departments/ endpoint: filter=$having:AVG(employees.salary)$gt:500000 - matches all departments where the average salary is greater than 100.000

Note that the $having: operator does not support nesting - it is only intended to be used on the top level of a filter.

The $like: operator supports wildcards for multi-character (*) and single-character (?) matches. Note that string comparisons are (normally) done lowercase, depending on the underlying database support.

Example: filter=name$like:K*so? - matches all resources with a name starting with "K", then s sequence of random characters, ending with "so" followed by any single character.

If no wildcard is present, the string will be prefixed and suffixed with a (*), effectively turning it into a "contains" expression.

Null values can be filtered using $null: and $nnull:.

Time-related values support various UTC-based formats for filtering. Specific time fields can be extracted for comparison.

The $in: and $nin: operators are used to filter by a list of values, which must be enclosed in brackets.

Examples of time field filtering:

• filter=birth_date$eq:01-25 matches all resources with a birthdate on January 25th.

• filter=birth_date$gte:1995-- matches all resources born in or after 1995.

• filter=last_login$gte:12:15:00 matches all resources who last logged in after 12:15:00.

• filter=last_login$gte:2023-01-01T00:00:00Z matches all resources who last logged in after 2023-01-01T00:00:00Z.

• filter=last_login$gte:2023-11-02T15:22:45.123+0200 matches all resources who last logged in after 2023-11-02T15:22:45.123+0200.

• filter=last_login$gte:2023-11-02T15:22[America/New_York] matches all resources who last logged in after 2023-11-02T15:22 in the America/New_York timezone.

Special characters are escaped with a dollar sign ($). Special characters include:

• The escape char itself: '$'

• Asterisk: '*'

• Question mark: '?'

• Left parenthesis: '('

• Right parenthesis: ')'

• Comma: ','

• Left bracket: '['

• Right bracket: ']'

• Colon: ':'

• Hyphen: '-'

• Space: ' '

Note that after escpaping is resolved, literal wildcards () and (?) are escaped with () and (?), respectively.

Example: filter=some_attribute$like:K$*soL$?p? becomes filter=name$like:K**soL??p?

This is done to prepare the string for LIKE or language specific regular expressions in the target query language.