用后缀模型对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)

​

我也想用续集模型做同样的事，我试过了，但我想我错过了一些东西。

包列表->列表

​

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() (和其他查询方法)是异步的，因此您需要解析承诺(或使用回调)来解析值，然后才能将list_ids传递给Op.notIn。它还将返回一个具有list_id属性的对象数组，因此您需要将其映射到整数数组，然后才能使用它。您还可以传入raw: true，这样它就不会从结果中生成后缀实例，而是返回普通的javascript对象--这比仅仅为了获取单个属性而创建对象更有效。

通过在required: false上设置Activities，您将返回所有List_sub_Activities，而不是对它们进行过滤(有些结果为null )。这可能不是你想要的。

这个示例使用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，这是将agency_id设置为2的JOINed到Activities，然后只返回List_sub_Activities.list_id为NULL的List_of_Packages的结果(在左联接上没有匹配)。这应该在一个查询中返回与上面相同的结果。

// 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),
});