entity-routes

The SearchFilter allows you to filter efficiently a list of items with several common SQL operators (such as =, IN, EXISTS, LIKE, etc) using readable query parameter keys. You can filter on nested relations/properties with complex conditions.

Decorator#

You can register a SearchFilter on an entity using the @Search decorator.

You can directly pass an array of FilterProperty, which is an array of property/nested property path or a tuple of property/nested property path with a specific default StrategyType. This list contains the filterable properties.

Each StrategyType will allow you to filter a property through different conditions.

You can use any StrategyType on a filterable property. Specifying one for a property path in the FilterProperty list just sets it as the default strategy to use for that property path and allows you to omit it in query parameters.

Example of use with a property, a property path with another defaultWhereStrategy and another global defaultWhereStrategy for that @Entity :

#typescript
1@Search(
2    ["firstName", ["role.identifier", "CONTAINS"]],
3    { defaultWhereStrategy: "STARTS_WITH" }
4)

In this example the firstName property will default to STARTS_WITH unless it is overriden by specifiying another StrategyType directly in the request, whereas the role.identifier property will default to CONTAINS strategy, unless overriden directly in the request.

Property decorator#

TODO

Good to know#

You can filter on the same property using different strategies in the same request.

You can filter on multiple values by passing comma-separated values. Those values will be trimmed.[More details.](#multiple-values)

Filtering on a relation will implicitly filter on its `id`. [More details.](#relations)

The `defaultWhereStrategy` applied (if no options are passed) is the `EXACT` [`StrategyType`](#strategies).[More details.](#default-config).

Basic Usage#

Once you have enabled the properties you want to filter, you can use them as query parameters on list operation for that @Entity.

On a User entity, by enabling only the firstName and role.identifier properties :

#typescript
1class AbstractEntity {
2    @PrimaryGeneratedColumn()
3    id: number;
4}
5
6@Entity()
7class Role extends AbstractEntity {
8    @Column()
9    identifier: string;
10}
11
12@Search(["firstName", ["role.identifier", "CONTAINS"]], { defaultWhereStrategy: "STARTS_WITH" })
13@Entity()
14class User extends AbstractEntity {
15    @Column()
16    firstName: string;
17
18    @ManyToOne(() => Role)
19    role: Role;
20}

With these query parameters :

#json
1{ "id": "123", "firstName": "Alex", "role": "789", "role.identifier": "abc456" }

On the request: GET:/users/?id=123&firstName=Alex&role=789&role.identifier=abc456

Only the firstName & role.identifier query parameters will be used as filtered since they alone were explicitly specified on @Search filterable properties.

firstName strategy is STARTS_WITH since it is the defaultWhereStrategy given in options and role.identifier strategy is CONTAINS since it was provided in properties (in FilterProperty tuple).

Strategies#

These strategies can be used both as defaultWhereStrategy or directly in query parameter with the advanced syntax.

StrategyType	Shortcut	SQL generated	Inverse SQL
EXACT	none	prop = value	!=
IS	none	prop IS value	IS NOT
IN	none	prop IN value	NOT IN
EXISTS	none	prop IS NOT NULL	IS NULL
CONTAINS	none	prop LIKE %value%	NOT LIKE %value%
STARTS_WITH	none	prop LIKE value%	NOT LIKE value%
ENDS_WITH	none	prop LIKE %value	NOT LIKE %value
BETWEEN	<>	prop IS BETWEEN value	IS NOT BETWEEN
BETWEEN_STRICT	><	(valueMin > prop AND prop < valueMax)	(valueMin <= prop AND prop >= valueMax)
LESS_THAN	<	prop < value	>=
LESS_THAN_OR_EQUAL	<\|	prop <= value	prop > value
GREATER_THAN	>	prop > value	<=
GREATER_THAN_OR_EQUAL	>\|	prop >= value	prop < value

If you're wondering why LESS_THAN_OR_EQUAL and GREATER_THAN_OR_EQUAL use the | instead of = character, it's because the = is already used to define the limit between the query parameter key and value.

Multiple values#

If you need to filter on the same FilterProperty and StrategyType but with different values, you can use comma-separated values as query param value. Then each of these values will be trimmed and then wrapped in the same SQL condition with a OR conditions for each value.

Passing an array of values to a query parameter with the strategy `EXACT` will be the same as using the `IN` strategy. Conversely, if you use the `NOT` operator by appending a `!` to the query parameter key, the `EXACT` with multiple values will end up with the same results as a `NOT IN`.

BETWEEN_STRICT is the only strategy that always requires exactly 2 values. Example: { "id><": "3, 8" } or { "role.id;betweenStrict": "3, 8" }

Relations#

You can filter a relation property by enabling a FilterProperty on its dot delimited path. Example: abc.def.ghi
You can filter on a relation by either enabling as FilterProperty the relation itself or by appending .id. Example: { "role": 123 } is the same as { "role.id": 123 }
There is no limit to the nesting, be careful with the SQL performance of every LEFT JOIN made if you nest too far

Advanced usage#

Since any StrategyType is available for a filterable property, sometimes you might want to use another one than the default one. To do so you just need to use the strategy part of the advanced syntax.

There is a syntax for query parameters keys that makes filters very powerful. There are 4 parts to a FilterProperty query parameter. Each part name is wrapped with parenthesis below (for readability, there are no parenthesis in usage) :

(complex condition)(propertyPath)(;StrategyType|shortcut)(!)

complex condition: Optional. The complex condition part might be used if you have advanced needs, it allows you to nest and group conditions with the and|or operators. More details here.
propertyPath: Using a dot delimited string, you can target a (shallow|deeply nested) (relation|property) to filter on.
StrategyType|shortcut: Optional. The StrategyType name (can be written camelCased or as UPPERCASE_SNAKE_CASE) prepended by a colon or just the shortcut (without the colon) if there is any available.
!: Optional. The NOT operator, by appending a ! to a query parameter key, the StrategyType will be inversed.

Quick example#

Below is an example which makes no actual sense whatsoever, still, it remains valid and would produce the following query.

With these query parameters :

#json
1{
2    "id!": "123",
3    "id;greaterThan!": "456",
4    "owner.id!": "789,321,654",
5    "and(key0):owner.role.identifier": "admin",
6    "and(key0):owner.role.name;endsWith": "@gmail.com",
7    "or(key1):owner.birthDate<>": "1996-01-01T00:00:00, 2000-01-01T00:00:00",
8    "or(key1)and(nestedKey2):owner.id>": "987",
9    "or(key1)or(nestedKey2):owner.email;STARTS_WITH!": "abc"
10}

A query would be produced looking like this :

#sql
1SELECT ... from teams team
2LEFT JOIN users team_owner ON team.owner_id = users.id
3LEFT JOIN roles team_owner_role ON team_owner_role.id = team_owner.role_id
4WHERE
5    team.id != 123
6AND team.id <= 456
7AND team_owner.id NOT IN (789, 321, 654)
8AND (
9        team_owner_role.identifier = "admin"
10    AND team_owner_role.name LIKE '%@gmail.com'
11)
12OR (
13        (team_owner.birthDate BETWEEN "1996-01-01T00:00:00" AND "2000-01-01T00:00:00")
14    AND (
15            team_owner.id > 987
16        OR team_owner.email NOT LIKE 'abc%'
17    )
18)

Full example#

#typescript
1class AbstractEntity {
2    @PrimaryGeneratedColumn()
3    id: number;
4}
5
6@Entity()
7class Role extends AbstractEntity {
8    @Column()
9    identifier: string;
10}
11
12@Search({ all: true })
13@EntityRoute({ operations: ["list"] })
14@Entity()
15class User extends AbstractEntity {
16    @Column()
17    firstName: string;
18
19    @ManyToOne(() => Role)
20    role: Role;
21}

On the user.list route scope (GET:/users/): Using all: true, you can filter on any property path from the User entity. Here the default strategy used for every FilterProperty is EXACT since it's the global default one and none were specified on the @Search decorator.

Filter description	Query parameter key	Query parameter value
Get all users with ids in list [1, 2, 3]	`id`	`1,2,3`
Get all users with role equal to 111	`role` or `role.id`	`111`
Get all super_admin users	`role.identifier`	`super_admin`
Get all admin users	`role.identifier;endsWith` or `role.identifier;ENDS_WITH`	`_admin`

Complex conditions#

At some point, you might need to have more complex conditions to filter your list, requiring the SQL OR operator, or with maybe some nested conditions. That is possible using the complex conditions syntax.

All you need for that is to prefix your condition with the and or or condition operator. When you have nested conditions you should append a condition identifier wrapped in parenthesis after the condition operator (and|or). That condition identifier can be any string you want, as long as you re-use the same consistently for the same conditions you want to nest.

You can chain them indefinitely and add as much nested conditions as you want. It is recommended to use names that makes sense to make the query parameter key almost readable like an english sentence.

You can omit the `and` operator, though you it is recommended to explicitly write it for readability. Example: `or(isAdmin)and(aboveId):id>` = `or(isAdmin)(aboveId):id>`

Example

Using the same entities as the example above.

For these query parameters :

#json
1{
2    "id!": 123,
3    "or(isAdminNotSuperOrNameLike)and:role.identifier;contains": "admin",
4    "or(isAdminNotSuperOrNameLike)and:role.identifier!": "super_admin",
5    "or(isAdminNotSuperOrNameLike)or:firstName;startsWith": "Alex"
6}

You would have that query generated :

#sql
1SELECT ... from users user
2LEFT JOIN roles user_role ON user_role.id = user.role_id
3WHERE
4    user.id != 123
5OR (
6    user_role.identifier LIKE '%admin%'
7    AND user_role.identifier != "super_admin"
8    OR user.firstName LIKE 'Alex%'
9)

Full example

To get all users with first name that starts with "Alex" that are either in the list [1, 2, 3] or that are admin and with an id above 50 but not super_admin unless his first name is "Max" or "Nath". (Yes this is a complete unrealistic demand, I just needed an example of a complex query.)

The query parameters would look like this :

#json
1{
2    "id": "1,2,3",
3    "firstName;startsWith": "Alex",
4    "or(isAdmin)and(aboveId):role.identifier;endswith": "_admin",
5    "or(isAdmin)and(aboveId):id>": "50",
6    "or(isAdmin)or(notSuperOrInList):role.identifier!": "super-admin",
7    "or(isAdmin)or(notSuperOrInList)or:firstName": "Max, Nath"
8}

Reminder: The complex condition names (isAdmin) and (aboveId) are completely arbitrary, you just need to use the same string when you want to nest conditions.

And the SearchFilter would generate a query like :

#sql
1SELECT ... from users user
2LEFT JOIN roles user_role ON user_role.id = user.role_id
3WHERE
4    user.id IN (1,2,3)
5AND user.firstName LIKE 'Alex%'
6OR (
7    (
8            user_role.identifier LIKE '%_admin'
9        AND user.id > 50
10    )
11    OR (
12            user_role.identifier != "super_admin"
13        OR user.firstName IN ("Max", "Nath")
14    )
15)

Default config#

You can get the default SearchFilter using the getSearchFilterDefaultConfig function. It can be used for example to retrieve the defaultWhereStrategy inside the options key.

Introduction

PaginationFilter