[Feature Request] Custom operators

[Diluka]

This feature allow developers to define new operators.

For example:

1. define a new operator json_contains template

(alias, values)=> `JSON_CONTAINS(${alias}, ${values})`

2. use like filter=roles||json_contains||ROLE_A,ROLE_B
3. generate

andWhere('JSON_CONTAINS(roles, :roles)',{roles: JSON.stringify(roles)})

[joeybenenati]

Is there any plan to implement this? This flexibility would nice as I'm now forked and manually adding postgis operators as I need.

[rgolea]

@joeybenenati how do you add postgis operators? Can you share some code? I need to use ST_DWithin but I'm not really sure how. Can you help?

[joeybenenati]

@rgolea

1. add operator to ComparisonOperator and CondOperator type in packages/crud-request/lib/types/request-query.types.d.ts
2. In packages/crud-typeorm/src/typeorm-crud.service.ts to the switch in mapOperatorsToQuery

case 'notnull':
        ...
        str = `${field} IS NOT NULL`;
        params = {};
        break;

      case 'intersects':
        str = `ST_Intersects(${field}, ST_GeomFromGeoJSON(:${param}))`;
        break;

      case 'between':
       ...

[rgolea]

@joeybenenati Wow! That was fast! Thank you very much!!! Maybe afterwards I can study the project better and do a PR to add new params.

Note for later:
Maybe setting an operator called custom and set inside CrudRequestOptions something called custom or maybe just in parameters so you can configure something like:

@Crud({
   params: {
       custom: {
            intersects: (
                  field: string,
                  param: any,
             ) => `ST_Intersects(${field}, ST_GeomFromGeoJSON(:${param}))`
       }
   }
})

And then just adding to the service something like:

case 'notnull':
        ...
        str = `${field} IS NOT NULL`;
        params = {};
        break;

      case 'custom':
        str = options.custom[operator?](field, param);
        break;

      case 'between':
       ...

It's a rough draft but maybe it's a start? @joeybenenati if you can, please tell me if this is something that you'd be interested in or maybe just help me guide the code (since I've just discovered this project recently and I haven't tested everything yet).

[rgolea]

@joeybenenati could you help me with testing this feature out? I will also add tests but at the moment I would like to know that it works. (Please see #253 )

[joeybenenati]

@rgolea sorry, I've since abandoned using this package for my current project. It's a great set of tools but I need a little more flexibility. I too had some trouble navigating and testing the project. I need to adhere to STAC API spec but would also like to incorporate a json based api query language simlar to loopback 3.

[Diluka]

this validation(see below) is preventing custom operators. But we can reuse the built-in operators to change their behaviors. for example #150 (comment) changes eq and in

crud/packages/crud-request/src/request-query.validator.ts

Lines 68 to 74 in 76dc6c2

	export function validateComparisonOperator(operator: ComparisonOperator): void {
	if (!comparisonOperatorsList.includes(operator)) {
	throw new RequestQueryException(
	`Invalid comparison operator. ${comparisonOperatorsListStr} expected`,
	);
	}
	}

[rgolea]

@Diluka the PR I pushed would have fixed the problem (hopefully)

[prevostc]

If that helps, I was able to implement a custom operator by overriding mapOperatorsToQuery on the service

  protected mapOperatorsToQuery(cond: QueryFilter, param: any): { str: string; params: ObjectLiteral } {
    if (cond.field === "tags") {
      let str: string
      let params: ObjectLiteral = { [param]: cond.value }

      const field = this.getFieldWithAlias(cond.field)
      switch (cond.operator) {
        case "$cont":
          str = `${field} @> :${param}::jsonb`
          params = { [param]: `${JSON.stringify(cond.value)}` }
          break
        default:
          str = `${field} = :${param}::jsonb`
          break
      }
      return { str, params }
    } else {
      return super.mapOperatorsToQuery(cond, param)
    }
  }

This is a bit of a manual work, but it could be generalised. And it makes sense to override the service protected methods to add new functionality.

Maybe all that is needed is a "Custom operator" section on the wiki.