Loopback filter on column "varchar []" does not work in PostgreSQL:

[bytewareteam]

Hi guys, I'm using postgres as a BD, and I have a column of type "varchar []", and I need to query the records that contain certain values in the array, this in SQL is represented by "columName" @> '{"v1" , "v2"} ', but couldn't find how to do this in the documentation.
Investigating a little more I find this issue, where they indicate that the problem would be solved in version "5.1.0" (version that I have installed):

In this issue we are redirected to the discussion where this error would be solved:

Still checking the repository code, they haven't really applied the change:

[bytewareteam]

First of all, I apologize for not expressing myself in the best way, as a result of desperation for not finding a solution to my problem.

Initially, I thought that the "inq" filter was poorly designed for the ":: varchar []" data type, however after some testing I realized that this operator is fully functional, it just wasn't what it was searching.
Taking postgres operators for array-type fields as a reference, that's when I really realized that it didn't work badly, but there were missing operators to declare, since the type of operations I needed are not available due to the loopback.

It was then that I started to read the package code in detail, to find a solution to my problem.
So I got to the buildExpression function used to interpret the loopback operators, however, to extend these operators, the only solution I could think of to test if my idea was correct, was to recreate this function in the model repository and along with it, buildSelect, toColumnValue and _buildWhere.
This way I can use my custom operators more easily.

My Repository

export class SegmentsWithScopeRepository extends DefaultCrudRepository<SegmentsWithScope,
  typeof SegmentsWithScope.prototype.matchId,
  SegmentsWithScopeRelations> {
  constructor(
    @inject('datasources.postgresDs') dataSource: PostgresDsDataSource,
  ) {
    super(SegmentsWithScope, dataSource);
  }

  customQuery(filter?: Filter) {
    const query = this.buildSelect(SegmentsWithScope.modelName, filter);
    return this.execute(query.sql, query.params);
  }

  buildSelect(model: string, filter?: Filter): ParameterizedSQL {
    if (!filter?.order) {
      const idNames = this.dataSource.connector!.idNames(model);
      if (idNames?.length) {
        filter = {
          ...filter ?? {},
          order: idNames,
        };
      }
    }

    const columnNames = this.dataSource.connector!.buildColumnNames(model, filter);
    const tableName = this.dataSource.connector!.tableEscaped(model);
    let selectStmt = new ParameterizedSQL(`SELECT ${columnNames} FROM ${tableName}`);

    if (filter) {
      if (filter.where) {
        const whereStmt = this.buildWhere(model, filter.where);
        selectStmt.merge(whereStmt);
      }

      if (filter.order) {
        selectStmt.merge(this.dataSource.connector!.buildOrderBy(model, filter.order));
      }

      if (filter.limit || filter.skip || filter.offset) {
        selectStmt = this.dataSource.connector!.applyPagination(
          model, selectStmt, filter,
        );
      }
    }
    return this.parameterize(selectStmt);
  }

  parameterize(ps: ParameterizedSQL): ParameterizedSQL {
    return this.dataSource.connector!.parameterize(ps);
  }

  buildWhere(model: string, where?: Where) {
    const whereClause = this._buildWhere(model, where);
    if (whereClause.sql) {
      whereClause.sql = 'WHERE ' + whereClause.sql;
    }
    return whereClause;
  }

  _buildWhere(model: string, where?: Where<SegmentsWithScope>): ParameterizedSQL {
    let columnValue, sqlExp;
    if (!where) {
      return new ParameterizedSQL('');
    }
    if (typeof where !== 'object' || Array.isArray(where)) {
      console.log('Invalid value for where: %j', where);
      return new ParameterizedSQL('');
    }
    const props = this.getModelDefinition(model).properties;

    const whereStmts = [];
    for (const key in where) {
      const stmt = new ParameterizedSQL('', []);
      // Handle and/or operators
      if (key === 'and' || key === 'or') {
        const branches = [];
        let branchParams: unknown[] = [];
        // eslint-disable-next-line @typescript-eslint/ban-ts-ignore
        // @ts-ignore
        const clauses: Where<SegmentsWithScope>[] = where[key];
        if (Array.isArray(clauses)) {
          for (let i = 0, n = clauses.length; i < n; i++) {
            const stmtForClause = this._buildWhere(model, clauses[i]);
            if (stmtForClause.sql) {
              stmtForClause.sql = '(' + stmtForClause.sql + ')';
              branchParams = branchParams.concat(stmtForClause.params);
              branches.push(stmtForClause.sql);
            }
          }
          stmt.merge({
            sql: branches.join(' ' + key.toUpperCase() + ' '),
            params: branchParams,
          });
          whereStmts.push(stmt);
          continue;
        }
        // The value is not an array, fall back to regular fields
      }
      let p = props[key];

      if (p == null && isNested(key)) {
        // See if we are querying nested json
        p = objectPath.get(props, key);
      }

      if (p == null) {
        // Unknown property, ignore it
        console.log('Unknown property %s is skipped for model %s', key, model);
        continue;
      }
      // eslint-disable-next-line @typescript-eslint/ban-ts-ignore
      // @ts-ignore
      let expression = where[key];
      const columnName = this.columnEscaped(model, key);
      // eslint-enable one-var
      if (expression === null || expression === undefined) {
        stmt.merge(columnName + ' IS NULL');
      } else if (expression && expression.constructor === Object) {
        const operator = Object.keys(expression)[0];
        // Get the expression without the operator
        expression = expression[operator];
        if (operator === 'inq' || operator === 'nin' || operator === 'between') {
          columnValue = [];
          if (Array.isArray(expression)) {
            // Column value is a list
            for (let j = 0, m = expression.length; j < m; j++) {
              columnValue.push(this.toColumnValue(p, expression[j], true));
            }
          } else {
            columnValue.push(this.toColumnValue(p, expression, true));
          }
          if (operator === 'between') {
            // BETWEEN v1 AND v2
            const v1 = columnValue[0] === undefined ? null : columnValue[0];
            const v2 = columnValue[1] === undefined ? null : columnValue[1];
            columnValue = [v1, v2];
          } else {
            // IN (v1,v2,v3) or NOT IN (v1,v2,v3)
            if (columnValue.length === 0) {
              if (operator === 'inq') {
                columnValue = [null];
              } else {
                // nin () is true
                continue;
              }
            }
          }
        } else if (operator === 'regexp' && expression instanceof RegExp) {
          // do not coerce RegExp based on property definitions
          columnValue = expression;
        } else {
          columnValue = this.toColumnValue(p, expression, true);
        }
        sqlExp = this.buildExpression(columnName, operator as Operators, columnValue, p);
        stmt.merge(sqlExp);
      } else {
        // The expression is the field value, not a condition
        columnValue = this.toColumnValue(p, expression);
        if (columnValue === null) {
          stmt.merge(columnName + ' IS NULL');
        } else {
          if (columnValue instanceof ParameterizedSQL) {
            if (p.type.name === 'GeoPoint')
              stmt.merge(columnName + '~=').merge(columnValue);
            else
              stmt.merge(columnName + '=').merge(columnValue);
          } else {
            stmt.merge({
              sql: columnName + '=?',
              params: [columnValue],
            });
          }
        }
      }
      whereStmts.push(stmt);
    }
    let params: unknown[] = [];
    const sqls = [];
    for (let k = 0, s = whereStmts.length; k < s; k++) {
      sqls.push(whereStmts[k].sql);
      params = params.concat(whereStmts[k].params);
    }
    return new ParameterizedSQL({
      sql: sqls.join(' AND '),
      params: params,
    });
  }

  toColumnValue(prop: PropertyDefinition, val: unknown, isWhereClause = false) {
    const isArrayDataType = dataTypeIsArray(prop.postgresql?.dataType);
    if (Array.isArray(prop.type)) {
      return Array.isArray(val)
        ? (isArrayDataType ? val : JSON.stringify(val))
        : (isArrayDataType ? JSON.parse(val as string) : val);
    }
    return this.dataSource.connector!.toColumnValue(prop, val, isWhereClause);
  }

  buildExpression(propName: string, operator: string, propValue: unknown, propertyDefinition: PropertyDefinition) {
    const isArray = dataTypeIsArray(propertyDefinition.postgresql?.dataType);
    let op;
    if (isArray) {
      switch (operator) {
        case 'overlap':
          op = '&&';
          break;
        case 'contains':
          op = '@>';
          break;
        case 'isContainedBy':
          op = '<@';
          break;
        default:
          return this.dataSource.connector!.buildExpression(
            propName, operator, propValue, propertyDefinition,
          );
      }
      const dataType = propertyDefinition.postgresql?.dataType;
      return new ParameterizedSQL(`${propName} ${op} ?::${dataType}`, [propValue]);
    }

    return this.dataSource.connector!.buildExpression(
      propName, operator, propValue, propertyDefinition,
    );
  }

  getModelDefinition(...args: unknown[]) {
    return this.dataSource.connector!.getModelDefinition(...args);
  }

  columnEscaped(...args: unknown[]) {
    return this.dataSource.connector!.columnEscaped(...args);
  }
}

const dataTypeIsArray = (type: string) => type.includes('[]');
const isNested = (prop: string) => prop.split('.').length > 1;

query Filter:

{
  "where": {
    "segments": {"contains": ["14", "18", "20"]},
    "formatDisplayId": 1
  }
}

I know that sooner or later I will have to move this code elsewhere, refactor and simplify, but at this moment that it works is enough for me.
if there is a better way to implement it i would like to know

[dhmlau]

@agnes512, could you please help? Thanks.

[bajtos]

The version 5.2.0 published on 2020-10-06 added support for contains operator, see #460. Can you @bytewareteam please give it a try?

In the code snippet you posted in #461 (comment), I see that you are implementing another custom operator isContainedBy. Would you like to open a pull request to contribute that operator to the connector? You can pretty much repeat the changes made in #460.

[bytewareteam]

Hello everyone, it has been a long time since I reviewed this issue for going on vacation, anyway.
I recently found out about this new operator that the truth is quite interesting, and it solves my initial approach. However, now I am working on implementing a way to declare custom operators, without resorting to rewriting library functions, since as we advance in our project, more operators are needed.
Since today I am rejoining the team, I will be able to inform you of new developments regarding this issue.
Thank you.