node.js - Sequelize，如何使用 Op.contains 找到具有特定值的模型

Question

我正在做一些关于出租房屋和公寓的项目，我已经到了需要根据房屋的功能(wifi、安全和其他员工)实现过滤房屋的地步。一开始我决定第一次尝试 Sequelize ORM。添加、创建、编辑之类的东西工作正常，但过滤部分是我遇到的一些问题。

我正在使用 nodejs、express 和 postgresql。我需要找到所有具有特征 ID 数组中列出的特征的房屋。这是我试过的。在此示例中，我尝试获取具有 ID 为 1、2 和 4 的特征的房屋。

db.House.findAll({
  include: [{
    model: db.HouseFeature, 
    as: 'HouseFeatures',
    where: {
      featureId: {
        [Op.contains]: [1, 2, 4] //<- array of featuresIds
      }  
    }
  }]
})

通过单个功能 ID 获取房屋工作正常，因为我不在那里使用 Op.contains。以下是与此案例相关的一些关系:

House.hasMany(models.HouseFeature, { onDelete: 'CASCADE' });
HouseFeature.belongsTo(models.House);

HouseFeature 包含 featureId 字段。这是我得到的错误:

error: оператор не существует: integer @> unknown
       at Connection.parseE (C:\***\server\node_modules\pg\lib\connection.js:601:11)
       at Connection.parseMessage (C:\***\server\node_modules\pg\lib\connection.js:398:19)
       at Socket.<anonymous> (C:\***\server\node_modules\pg\lib\connection.js:120:22)
       at Socket.emit (events.js:182:13)
       at addChunk (_stream_readable.js:283:12)
       at readableAddChunk (_stream_readable.js:264:11)
       at Socket.Readable.push (_stream_readable.js:219:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     name: 'error',
     length: 397,
     severity: 'ОШИБКА',
     code: '42883',
     detail: undefined,
     hint:
      'Оператор с данными именем и типами аргументов не найден. Возможно, вам следует добавить явные приведения типов.',
     position: '851',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file:
      'd:\\pginstaller.auto\\postgres.windows-x64\\src\\backend\\parser\\parse_oper.c',
     line: '731',
     routine: 'op_error',
     sql:
      'SELECT "House"."id", "House"."title", "House"."description", "House"."price", "House"."address", "House"."lat", "House"."lon", "House"."kitchen", "House"."bathrooms", "House"."floor", "House"."totalFloors", "House"."people", "House"."area", "House"."bedrooms", "House"."trusted", "House"."createdAt", "House"."updatedAt", "House"."CityId", "House"."ComplexId", "House"."OwnerProfileId", "House"."HouseTypeId", "House"."RentTypeId", "HouseFeatures"."id" AS "HouseFeatures.id", "HouseFeatures"."featureId" AS "HouseFeatures.featureId", "HouseFeatures"."createdAt" AS "HouseFeatures.createdAt", "HouseFeatures"."updatedAt" AS "HouseFeatures.updatedAt", "HouseFeatures"."HouseId" AS "HouseFeatures.HouseId" FROM "Houses" AS "House" INNER JOIN "HouseFeatures" AS "HouseFeatures" ON "House"."id" = "HouseFeatures"."HouseId" AND "HouseFeatures"."featureId" @> \'1,2\';'

对不起那里的一些俄罗斯人。

更新:

通过更改仅与一个 HouseFeature 相关的每个 House，并更改该 HouseFeature 模型以存储 featureId 数组，我已经设法完成我需要的工作。 Op.contains 工作正常。

  db.House.findAll({
      include: [{
        model: db.HouseFeature, 
        as: 'HouseFeature',
        where: {
          features: {
            [Op.contains]: req.body.features
          }  
        },
      }]     
  })
  // Associations
  HouseFeature.belongsTo(models.House);
  House.hasOne(models.HouseFeature, { onDelete: 'CASCADE' });

  const HouseFeature = sequelize.define('HouseFeature', {
    features: {
      type: DataTypes.ARRAY(DataTypes.INTEGER)
    }
  }, {});

现在我有一个小问题。我可以以某种方式将 HouseFeature 模型与 Feature 模型链接起来，以便稍后获取特征图标图像和名称吗？特征 ID 存储在 HouseFeature 数组中。

Answer 1

请检查Op.in和Op.contains的区别:

[Op.in]: [1, 2], // IN [1, 2]

[Op.contains]: [1, 2] // @> [1, 2] (PG array contains operator)

看起来 HouseFeatures.featureId 是一个类型为 integer 的 PK，而不是 postgres array。

请尝试:

db.House.findAll({
  include: [{
    model: db.HouseFeature, 
    as: 'HouseFeatures',
    where: {
      featureId: {
        [Op.in]: [1, 2, 3]
      }  
    }
  }]
})

甚至

db.House.findAll({
  include: [{
    model: db.HouseFeature, 
    as: 'HouseFeatures',
    where: {
      featureId: [1, 2, 3]
    }
  }]
})

代替