Like operator not working when trying to fetch stringify array of objects or object
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
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.
- 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)
}
}
- 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
- Sin Dockerfile ni archivo de Docker Compose
- Tiene una plantilla de pull request
- Leer la guía de contribución
Primeros pasos
- Lee el issue completo y luego la guía de contribución del proyecto.
- Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
- Haz un fork del repositorio y trabaja en una rama.
- Abre un pull request que haga referencia al número del issue.
Más de loopbackio/loopback-connector-mssql
-
feature
Dificultad 5/5 Más de una semana Aptitud para principiantes 25/100
loopbackio/loopback-connector-mssql#251 · 2 reacciones ·
-
bug
Dificultad 4/5 3-5 días Aptitud para principiantes 35/100
loopbackio/loopback-connector-mssql#250 · 1 comentario · 1 reacción ·
-
bug
Dificultad 3/5 1-2 días Aptitud para principiantes 42/100
-
bug
Dificultad 3/5 1-2 días Aptitud para principiantes 35/100
loopbackio/loopback-connector-mssql#87 · 2 comentarios ·
Todos los issues de loopbackio/loopback-connector-mssql
Issues similares
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 82/100
ipfs/ipfs-webui#2507 ·
-
Environments table lists page size 5 after 30Posiblemente ocupada @raus7n la tomó hoy. Abierto
Dificultad 1/5 Menos de una hora Aptitud para principiantes 90/100
litmuschaos/litmus#5743 ·
Los mantenedores suelen responder en 2 días
-
bug
Dificultad 2/5 1-3 horas Aptitud para principiantes 63/100
anothaDev/luci-theme-vantage#143 ·
Los mantenedores suelen responder en 1 día
-
desktop grp:admin-donors type:graphics-bug ui-admin
Dificultad 2/5 1-3 horas Aptitud para principiantes 72/100
Los mantenedores suelen responder en 1 día
-
Dificultad 2/5 1-3 horas Aptitud para principiantes 72/100
kingcharleslzy-ai/dsh-purge#282 ·
Los mantenedores suelen responder en 1 día