javascript - 如何在Sequelize中使用

我有这个查询：

WITH tempA AS (SELECT conversationId FROM participant WHERE userId = "aaa"),
     tempB AS (SELECT conversationId FROM participant WHERE userId = "bbb")
SELECT conversationId
FROM tempA, tempB
WHERE tempA.conversationId = tempB.conversationId;

该查询将返回两个用户都参与的对话的ID。

我在Sequelize中也有一个参与模型：

const Participation = sequelize.define("participation", {
    //...attributes
});

module.exports = Participation;

如何在不使用sequelize.query的情况下在Sequelize中进行上述查询？

最佳答案

您可以使用scopes获得相同的效果。这是一个粗略的示例：

   /* for the ON clause of JOIN */
   participation.hasMany(participation, {
      sourceKey : 'userId',
      foreignKey: 'userId',
      as: 'selfJoin'
      });

   participation.addScope('tempA', {
      attributes: ['message_id'],
      where: {userId: 'aaa'}
      });

  participation.addScope('tempB',{
      attributes: ['message_id'],
      where: {userId: 'bbb'}
      });


   participation.scope('tempB').findAll({
      attributes: ['message_id'],
      include: [{
         model: participation.scope('tempA'),
         required: true,
         as: 'selfJoin',
         attributes: []
        }]
     });