node.js - 对字符串数组进行 Sequelize Op.overlap

Question

我有一个表，其中包含一个名为 tags 的字符串数组。
数据如下所示:
{ id: 1, 标签: ['football', 'basketball'] }, { id: 2, 标签: ['football', 'hockey'] }
用户可以输入多个字符串来查找在标签字段中包含至少一个查询的所有记录。
查询示例:['football', 'baseball']
为了实现这一点，我尝试使用 Op.overlap 但它不起作用。

db.Submission.findAll({
  where: {
    tags: {
      [Op.overlap]: queries, // ['football', 'baseball']
    }
  }
});

你对我有什么建议吗？

Answer 1

运算符 Op.overlap 应该可以工作。注意:这是一个 Postgres-only Range Operator
环境:

"sequelize": "^5.21.3"

postgres:9.6

例如。

import { sequelize } from '../../db';
import { Model, DataTypes, Op } from 'sequelize';

class Submission extends Model {}
Submission.init(
  {
    tags: DataTypes.ARRAY(DataTypes.STRING),
  },
  { sequelize, tableName: 'submissions' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await Submission.bulkCreate([
      { tags: ['football', 'basketball'] },
      { tags: ['football', 'hockey'] },
      { tags: ['a', 'b'] },
    ]);

    // test
    const queries = ['football', 'baseball'];
    const data = await Submission.findAll({
      where: {
        tags: {
          [Op.overlap]: queries,
        },
      },
      raw: true,
    });
    console.log(data);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

执行结果:

Executing (default): DROP TABLE IF EXISTS "submissions" CASCADE;
Executing (default): DROP TABLE IF EXISTS "submissions" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "submissions" ("id"   SERIAL , "tags" VARCHAR(255)[], PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'submissions' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "submissions" ("id","tags") VALUES (DEFAULT,ARRAY['football','basketball']::VARCHAR(255)[]),(DEFAULT,ARRAY['football','hockey']::VARCHAR(255)[]),(DEFAULT,ARRAY['a','b']::VARCHAR(255)[]) RETURNING *;
Executing (default): SELECT "id", "tags" FROM "submissions" AS "Submission" WHERE "Submission"."tags" && ARRAY['football','baseball']::VARCHAR(255)[];
[
  { id: 1, tags: [ 'football', 'basketball' ] },
  { id: 2, tags: [ 'football', 'hockey' ] }
]

数据库中的数据记录: