Postgresql search in array of text crashes

Help me!
1

Feature(s) impacted

Listing of records when filtering

Observed behavior

“Your server encountered an error”

Expected behavior

Displayed records

Failure Logs

SELECT COUNT(*) AS "__aggregate__" FROM "products" AS "Product" WHERE "Product"."id" ILIKE '%smartly%' OR "Product"."gen_features_en" @> ARRAY['smartly']::VARCHAR(255)[] 
 ORDER BY "__aggregate__" DESC NULLS LAST;
ERROR:  operator does not exist: text[] @> character varying[]
LINE 1: ... ILIKE '%smartly%' OR "Product"."gen_features_en" @> ARRAY['...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

It does not seem to correctly understand that gen_features_en is a field of text and not varchar

Context

  • Project name: Certainty
  • Team name: Certainty
  • Environment name: production
  • Agent technology: nodejs
  • Agent (forest package) name & version:
    “@forestadmin/agent”: “1.51.0”,
    “@forestadmin/datasource-sequelize”: “1.10.5”,
  • Database type: postgresql
  • Recent changes made on your end if any: none
2

Hello @Quentin_Farizon,

Sadly I do not reproduce, my text[] field is properly handled and the value is not being cast to varchar.

Could you tell me your Sequelize version as well as your postgres database version ?

3

I don’t reproduce by creating the same query manually :

  await Product.findAll({
    where: {
      genFeaturesEn: {
        [Op.contains]: ['smartly']
      }
    }
  })

This generates :
WHERE "Product"."gen_features_en" @> ARRAY['smartly']

I am on Sequelize 7.0.0-alpha.37 and postgres 15.4

4

It does work for me as well with those version, do you mind sharing the schema of both your db and sequelize model ? The field definition should be enough.

e.g:
postgres:

 certificates text[] COLLATE pg_catalog."default",

Sequelize:


 certificates: DataTypes.ARRAY(DataTypes.TEXT),
5 
@Table({ underscored: true })
export class Product extends Sequelize.Model<InferAttributes<Product>, InferCreationAttributes<Product>> {
  @Attribute(DataTypes.ARRAY(DataTypes.TEXT))
  @NotNull
  @Default([])
  declare genFeaturesFr: CreationOptional<string[]>
}

 gen_features_en                   | text[]                   |           | not null | ARRAY[]::text[]
6

You have shared genFeaturesFr but I supposed genFeaturesEn is declared in the same way ?

7

After looking into the details, I confirm that the issue is not on how the query is constructed.

Moreover we do not support Sequelize v7 yet, if you are able to reproduce the issue on a supported version of Sequelize we can try to look into it a bit more. I’m also wondering how you were able to launch the agent with Sequelize v7

8

I have debugged and I noticed the issue in @forestadmin/datasource-sequelize

In collection.js, in aggregate function, filter.conditionTree contains correct column name genFeaturesEn. But the output of this.queryConverter.getWhereFromConditionTree(filter.conditionTree) contains snake_case column name gen_features_en. Then Sequelize does not map it to the column (and then does not retrieve the correct type)

I suspect that Sequelize v6 was more permissive with the “underscored” settings and accepted both camelCase and underlying snake_case syntax

As a quick-patch, In un-ambigous.js, in getRealFieldName, I switched from fieldModel.field to fieldModel.attributeName
I’m not sure why you try to get the fieldName, even in v6 it is always preferred to use attributeName and not underscored ones

9

The property attributeName only exist in Sequelize v7.alpha, it is not present in Sequelize v6 as I’ve stated earlier we are not compatible with v7.alpha as of now.
Moreover using the attributeName, is not possible for some use cases that Forest Admin leverages, as some filters are done cross collection, passing through relations, the un-ambigous helper allows to define the direct path to the attribute in the db, meaning that we are not always using the defined sequelize models.

If you encounter any issues with a version of Sequelize v6, we would be happy to help and/or provide fixes when a bug is found.