javascript - 使用 Sequelize 模型 Sequelize Op.notIn

Question

你好，我有一个 mysql 查询，它在 sequelize.query 中工作正常，查询是

select list_name from lists l where l.list_id not in
(SELECT sub.list_id from list_sub_activities sub left join.
 Activities a on a.list_act_id = sub.list_act_id where a.agency_id = 2)

我想使用 Sequelize 模型做同样的事情，我已经尝试过，但我想我错过了一些东西。
包列表 ---> 列出

List_of_Packages.findAll({
  attributes: ['list_name'],
  where: {
    list_id: {
      [Op.notIn]: [List_sub_Activities.findAll({
        attributes: ['list_id'],
        include: {
          model: Activities,
          required: false,
          where: {
            agency_id: 2
          }
        }
      })
      ]
    }
  }


}).then((response) => {
  console.log(response);
})

如果你帮助我，我很感激。
谢谢!!!

Answer 1

findAll()(和其他查询方法)是异步的，因此您需要先解析 promise(或使用回调)来解析值，然后才能将 list_id s 传递给 Op.notIn 。它还将返回一个具有 list_id 属性的对象数组，因此您需要将其映射到一个整数数组，然后才能使用它。您还可以传入 raw: true，这样它就不会从您的结果中生成 Sequelize 实例，而是返回普通的 javascript 对象——这比创建对象只是为了获取单个属性更有效。
通过在 required: false include 上设置 Activities，您将返回所有 List_sub_Activities 而不会对它们进行过滤(有些在您的结果中将为空)。这可能不是您想要的。
为了清楚起见，此示例使用 async/await 而不是 thenables 。请注意，这是 而不是 最有效，因为它需要多个数据库查询，理想的解决方案是使用 LEFT JOIN 然后删除 package.list_id IS NULL 所在的项目(参见第二个示例)。

// get an array of Activities with the list_id set
const activities = await List_sub_Activities.findAll({
  attributes: ['list_id'],
  include: {
    model: Activities,
    // don't use required: false to only return results where List_sub_Activities.Activities is not null
    // required: false,
    where: {
      agency_id: 2,
    },
  },
  raw: true,
});

// map the property to an array of just the IDs
const activityIds = activities.map((activity) => activity.list_id);

// now you can pass the activityIds to Op.notIn
const packages = await List_of_Packages.findAll({
  attributes: ['list_name'],
  where: {
    list_id: {
      [Op.notIn]: activityIds,
    },
  },
});

与然后。

List_sub_Activities.findAll(...)
.then((activities) => activities.map((activity) => activity.list_id))
.then((activityIds) => List_of_Packages.findAll(...))
.then((packages) => {
  console.log(packages);
});

此示例将 List_of_Packages 与 List_sub_Activities LEFT JOINs 连接到 Activities，其中 WHERE 将 agency_id 设置为 2，然后仅返回 List_of_Packages 的结果，其中 List_sub_Activities.list_id 为 NULL(LEFT JOIN 上没有匹配的内容)。这应该在单个查询中返回与上述相同的结果。

// Get List_of_Packages where there is no match in List_sub_Activities after 
// it is joined to Activities with the agency_id set.
const agencyId = 2;
const packages = await List_of_Packages.findAll({
  attributes: ['list_name'],
  include: {
    model: List_sub_Activities,
    // we don't need to actually fetch the list_id
    attributes: [],
    include: {
      model: Activities,
      where: {
        agency_id: agencyId,
      },
    },
    // uses a LEFT JOIN
    required: false,
  },
  // only return results where the List_sub_Activities.list_id is null
  where: sequelize.where(sequelize.col('List_sub_Activities.list_id'), 'IS', null),
});