You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Loopback filter on column "varchar []" does not work in PostgreSQL: #461
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:
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
exportclassSegmentsWithScopeRepositoryextendsDefaultCrudRepository<SegmentsWithScope,typeofSegmentsWithScope.prototype.matchId,SegmentsWithScopeRelations>{constructor(
@inject('datasources.postgresDs')dataSource: PostgresDsDataSource,){super(SegmentsWithScope,dataSource);}customQuery(filter?: Filter){constquery=this.buildSelect(SegmentsWithScope.modelName,filter);returnthis.execute(query.sql,query.params);}buildSelect(model: string,filter?: Filter): ParameterizedSQL{if(!filter?.order){constidNames=this.dataSource.connector!.idNames(model);if(idNames?.length){filter={
...filter??{},order: idNames,};}}constcolumnNames=this.dataSource.connector!.buildColumnNames(model,filter);consttableName=this.dataSource.connector!.tableEscaped(model);letselectStmt=newParameterizedSQL(`SELECT ${columnNames} FROM ${tableName}`);if(filter){if(filter.where){constwhereStmt=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,);}}returnthis.parameterize(selectStmt);}parameterize(ps: ParameterizedSQL): ParameterizedSQL{returnthis.dataSource.connector!.parameterize(ps);}buildWhere(model: string,where?: Where){constwhereClause=this._buildWhere(model,where);if(whereClause.sql){whereClause.sql='WHERE '+whereClause.sql;}returnwhereClause;}_buildWhere(model: string,where?: Where<SegmentsWithScope>): ParameterizedSQL{letcolumnValue,sqlExp;if(!where){returnnewParameterizedSQL('');}if(typeofwhere!=='object'||Array.isArray(where)){console.log('Invalid value for where: %j',where);returnnewParameterizedSQL('');}constprops=this.getModelDefinition(model).properties;constwhereStmts=[];for(constkeyinwhere){conststmt=newParameterizedSQL('',[]);// Handle and/or operatorsif(key==='and'||key==='or'){constbranches=[];letbranchParams: unknown[]=[];// eslint-disable-next-line @typescript-eslint/ban-ts-ignore// @ts-ignoreconstclauses: Where<SegmentsWithScope>[]=where[key];if(Array.isArray(clauses)){for(leti=0,n=clauses.length;i<n;i++){conststmtForClause=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}letp=props[key];if(p==null&&isNested(key)){// See if we are querying nested jsonp=objectPath.get(props,key);}if(p==null){// Unknown property, ignore itconsole.log('Unknown property %s is skipped for model %s',key,model);continue;}// eslint-disable-next-line @typescript-eslint/ban-ts-ignore// @ts-ignoreletexpression=where[key];constcolumnName=this.columnEscaped(model,key);// eslint-enable one-varif(expression===null||expression===undefined){stmt.merge(columnName+' IS NULL');}elseif(expression&&expression.constructor===Object){constoperator=Object.keys(expression)[0];// Get the expression without the operatorexpression=expression[operator];if(operator==='inq'||operator==='nin'||operator==='between'){columnValue=[];if(Array.isArray(expression)){// Column value is a listfor(letj=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 v2constv1=columnValue[0]===undefined ? null : columnValue[0];constv2=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 truecontinue;}}}}elseif(operator==='regexp'&&expressioninstanceofRegExp){// do not coerce RegExp based on property definitionscolumnValue=expression;}else{columnValue=this.toColumnValue(p,expression,true);}sqlExp=this.buildExpression(columnName,operatorasOperators,columnValue,p);stmt.merge(sqlExp);}else{// The expression is the field value, not a conditioncolumnValue=this.toColumnValue(p,expression);if(columnValue===null){stmt.merge(columnName+' IS NULL');}else{if(columnValueinstanceofParameterizedSQL){if(p.type.name==='GeoPoint')stmt.merge(columnName+'~=').merge(columnValue);elsestmt.merge(columnName+'=').merge(columnValue);}else{stmt.merge({sql: columnName+'=?',params: [columnValue],});}}}whereStmts.push(stmt);}letparams: unknown[]=[];constsqls=[];for(letk=0,s=whereStmts.length;k<s;k++){sqls.push(whereStmts[k].sql);params=params.concat(whereStmts[k].params);}returnnewParameterizedSQL({sql: sqls.join(' AND '),params: params,});}toColumnValue(prop: PropertyDefinition,val: unknown,isWhereClause=false){constisArrayDataType=dataTypeIsArray(prop.postgresql?.dataType);if(Array.isArray(prop.type)){returnArray.isArray(val)
? (isArrayDataType ? val : JSON.stringify(val))
: (isArrayDataType ? JSON.parse(valasstring) : val);}returnthis.dataSource.connector!.toColumnValue(prop,val,isWhereClause);}buildExpression(propName: string,operator: string,propValue: unknown,propertyDefinition: PropertyDefinition){constisArray=dataTypeIsArray(propertyDefinition.postgresql?.dataType);letop;if(isArray){switch(operator){case'overlap':
op='&&';break;case'contains':
op='@>';break;case'isContainedBy':
op='<@';break;default:
returnthis.dataSource.connector!.buildExpression(propName,operator,propValue,propertyDefinition,);}constdataType=propertyDefinition.postgresql?.dataType;returnnewParameterizedSQL(`${propName}${op} ?::${dataType}`,[propValue]);}returnthis.dataSource.connector!.buildExpression(propName,operator,propValue,propertyDefinition,);}getModelDefinition(...args: unknown[]){returnthis.dataSource.connector!.getModelDefinition(...args);}columnEscaped(...args: unknown[]){returnthis.dataSource.connector!.columnEscaped(...args);}}constdataTypeIsArray=(type: string)=>type.includes('[]');constisNested=(prop: string)=>prop.split('.').length>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
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.
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.
Activity
bytewareteam commentedon Sep 29, 2020
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
buildExpressionfunction 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,toColumnValueand_buildWhere.This way I can use my custom operators more easily.
My Repository
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 commentedon Oct 20, 2020
@agnes512, could you please help? Thanks.
bajtos commentedon Nov 26, 2020
The version
5.2.0published on 2020-10-06 added support forcontainsoperator, 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 commentedon Nov 26, 2020
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.