Like operator not working when trying to fetch stringify array of objects or object

Abierto
#248 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
3/5
Tiempo estimado
1-2 días
Aptitud para principiantes
42/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Estancado
Stack tecnológico
javascript, nodejs, sql
Área
databases

Línea de trabajo

Comienza en loopback-connector-mssql/lib/mssql.js, en MsSQL.prototype.buildExpression, cerca de la línea 509 indicada, y reproduce la consulta like con una propiedad que contenga un objeto o un array de objetos en MSSQL. Compárala con el caso funcional de un array de strings y verifica que el parámetro generado conserve el patrón previsto. Se considera terminado cuando la consulta encuentra datos de objetos convertidos a string que coincidan, sin romper otros operadores like.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

bug

Have tried to search for similar issue, but could not find any.

Basically, I am trying to use like operator in loopback4 filter for columns that store stringified array of objects or just an object.

Because array of objects and object are stored as a string, you would normally use the like operator to find value in the string.

I am not sure if its a bug, can someone please help me on this?

Also, I am not sure if this happens on other SQL database as well.

Steps to reproduce

Here are the steps to reproduce.

  1. Create a Loopback 4 Model as following or any but have one field with property array of objects.
@model({settings: {'strict': true}})
export class LocationLog extends Entity {

    @property({
        type: 'string',
        id: true,
        defaultFn: 'guid'
    })
    id?: string


    @property.array(Object, {
        mssql: {
            dataType: 'nvarchar',
            dataLength: 'max'
        }

    })
    sentTo?: Object[]

    constructor(data?: Partial<Approval>) {
        super(data)
    }
}
  1. Fetch the data using Loopback 4 filter like so.

const filter = {
      
    where: {
         sentTo: {  like: '&anystringyouwanttofindusinglikeoperator&'  }
   }
}

// use the find method to fetch data
this.find(filter)

Basically, only two steps, of course you definitely need the controller and repository created as well, I won't be covering that.
Use loopback-cli to quickly generate the repository and controller for the model.

Current Behavior

When you try to fetch the data through .find() with the filter above, nothing will be returned even though there are data in the database.

I tried to track the problem and I found that in the file loopback-connector-mssql/lib/mssql.js, line 509 where the codes try to build the expression related to like operator.

If you console.log the operatorValue, you will find the the value has double quotations. Like so.

MsSQL.prototype.buildExpression = function(columnName, operator, operatorValue,
  propertyDefinition) {
  
  // console.log the parameters
 console.log(columnName, 'columnName')
console.log(operator, 'operator')
console.log(operatorValue, 'operatorValue')
console.log(propertyDefinition, 'propertyDefinition')

  switch (operator) {
    case 'like':
      return new ParameterizedSQL(columnName + " LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'nlike':
      return new ParameterizedSQL(columnName + " NOT LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'regexp':
      g.warn('{{Microsoft SQL Server}} does not support the regular ' +
          'expression operator');
    default:
      // invoke the base implementation of `buildExpression`
      return this.invokeSuper('buildExpression', columnName, operator,
        operatorValue, propertyDefinition);
  }
};

//result of console.log

[sentTo] columnName
like operator
"&anystringyouwanttofindusinglikeoperator&" operatorValue
{ ... } propertyDefinition

The operatorValue has double quotation which then will just be treated as a string in the like statement.

Because I am using MSSQL database, I understand that an array of objects or object will be stringified before being stored in the database.

Expected Behavior

The operatorValue should not have a double quotation.
It should be something like this.

[sentTo] columnName
like operator
&anystringyouwanttofindusinglikeoperator& operatorValue
{ ... } propertyDefinition

Additional information

Please note this will only happen if you have a model with field that is an array of objects or if that field is an object itself.
If you have an array of string, it works just fine.

I guess its probably because of how the code treats object which will always get stringified.

To fix this problem for my application, I modified the code like so,

MsSQL.prototype.buildExpression = function(columnName, operator, operatorValue,
  propertyDefinition) {

  // check for propertyDefinition first, only checks for object
  if(propertyDefinition.type.name === 'Object' || typeof(propertyDefinition.type) === 'object'){
    
    // only if operator is `like`
    if(operator === 'like'){

      // check if double quotation is present as the first and last char
      if(operatorValue[0] === '"' && operatorValue[operatorValue.length - 1] === '"'){
        // remove the double quotations
        operatorValue = operatorValue.slice(1, -1)
      }
    }
  }

  switch (operator) {
    case 'like':
      return new ParameterizedSQL(columnName + " LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'nlike':
      return new ParameterizedSQL(columnName + " NOT LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'regexp':
      g.warn('{{Microsoft SQL Server}} does not support the regular ' +
          'expression operator');
    default:
      // invoke the base implementation of `buildExpression`
      return this.invokeSuper('buildExpression', columnName, operator,
        operatorValue, propertyDefinition);
  }
};

The above code checks if the column is an object and remove the double quotations if present

node -e 'console.log(process.platform, process.arch, process.versions.node)';

win32 x64 16.14.2

Lenguaje dominante
JavaScript
Estrellas
52
Forks
80
Métricas de merge de PR
Sin PR fusionados en 30 d

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de loopbackio/loopback-connector-mssql

Todos los issues de loopbackio/loopback-connector-mssql

Issues similares

Más issues de JavaScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.