Sequelize -如何在嵌套关联中使用where子句？

Question

我有一系列的续集模型，它们都是相互关联的-

1. Userand Institution (通过Invitation.
2. Userand Institution与Event相关)也通过UserDetail模型相互关联。

我试图查询一个事件，并急切地加载与它相关的所有机构。当急切地加载机构时，我试图包含与该机构相关联的用户，但我只想要与事件关联的用户。我尝试过如下所示的查询，但是where子句说'$events.id$': req.params.eventId似乎不起作用--我一直从Sequelize获得一个missing FROM-clause entry for table \"events\"错误，但是Sequelize的SQL输出给了我一个明显的FROM \"Events\" AS \"Event\"。

有什么想法可以让这个查询工作吗？

查询

const event = await Event.findByPk(req.params.eventId, {
        include: [
          {
            model: Institution,
            as: 'institutions',
            attributes: ['id', 'name'],
            through: {
              model: Invitation
            },
            include: [
              {
                model: User,
                as: 'users',
                where: {
                  '$events.id$': req.params.eventId
                },
                attributes: ['id', 'name'],
                through: {
                  model: UserDetails,
                  attributes: []
                },
                include: [
                  {
                    model: Event,
                    as: 'events',
                    attributes: [],
                    through: {
                      model: Invitation,
                      attributes: []
                    }
                  }
                ]
              }
            ]
          }
        ]
      })

模型

// user.js model
User.belongsToMany(models.Institution, {
  through: models.UserDetails,
  as: 'institutions',
  foreignKey: 'userId',
  otherKey: 'institutionId'
})

User.belongsToMany(models.Event, {
  through: models.Invitation,
  as: 'events',
  foreignKey: 'userId',
  otherKey: 'eventId'
})

User.hasMany(models.Invitation, {
  as: 'invitations',
  foreignKey: 'userId'
})

// institution.js model

Institution.belongsToMany(models.User, {
  through: models.UserDetails,
  as: 'users',
  foreignKey: 'institutionId',
  otherKey: 'userId'
})

Institution.belongsToMany(models.Event, {
  through: models.Invitation,
  as: 'events',
  foreignKey: 'institutionId',
  otherKey: 'eventId'
})

Institution.hasMany(models.Invitation, {
  as: 'invitations',
  foreignKey: 'institutionId'
})

// event.js modedl
Event.belongsToMany(models.User, {
  through: models.Invitation,
  as: 'users',
  foreignKey: 'eventId',
  otherKey: 'userId'
})

Event.belongsToMany(models.Institution, {
  through: models.Invitation,
  as: 'institutions',
  foreignKey: 'eventId',
  otherKey: 'institutionId'
})

// invitation.js model
Invitation.belongsTo(models.User, {
  foreignKey: 'userId',
   onDelete: 'CASCADE'
})

Invitation.belongsTo(models.Institution, {
  foreignKey: 'institutionId',
  onDelete: 'CASCADE'
})

SQL输出

SELECT
   \"Event\".\"id\",
   \"Event\".\"name\",
   \"Event\".\"date\",
   \"Event\".\"description\",
   \"Event\".\"venue\",
   \"Event\".\"type\",
   \"Event\".\"notes\",
   \"Event\".\"images\",
   \"Event\".\"createdAt\",
   \"Event\".\"updatedAt\",
   \"institutions\".\"id\" AS \"institutions.id\",
   \"institutions\".\"name\" AS \"institutions.name\",
   \"institutions -> Invitation\".\"id\" AS \"institutions.Invitation.id\",
   \"institutions -> Invitation\".\"eventId\" AS \"institutions.Invitation.eventId\",
   \"institutions -> Invitation\".\"userId\" AS \"institutions.Invitation.userId\",
   \"institutions -> Invitation\".\"institutionId\" AS \"institutions.Invitation.institutionId\",
   \"institutions -> Invitation\".\"paid\" AS \"institutions.Invitation.paid\",
   \"institutions -> Invitation\".\"status\" AS \"institutions.Invitation.status\",
   \"institutions -> Invitation\".\"createdAt\" AS \"institutions.Invitation.createdAt\",
   \"institutions -> Invitation\".\"updatedAt\" AS \"institutions.Invitation.updatedAt\",
   \"institutions -> users\".\"id\" AS \"institutions.users.id\",
   \"institutions -> users\".\"name\" AS \"institutions.users.name\",
   \"institutions -> users -> UserDetails\".\"id\" AS \"institutions.users.UserDetails.id\",
   \"institutions -> users -> UserDetails\".\"userId\" AS \"institutions.users.UserDetails.userId\",
   \"institutions -> users -> UserDetails\".\"institutionId\" AS \"institutions.users.UserDetails.institutionId\",
   \"institutions -> users -> UserDetails\".\"contactPoint\" AS \"institutions.users.UserDetails.contactPoint\",
   \"institutions -> users -> UserDetails\".\"createdAt\" AS \"institutions.users.UserDetails.createdAt\",
   \"institutions -> users -> UserDetails\".\"updatedAt\" AS \"institutions.users.UserDetails.updatedAt\",
   \"institutions -> users -> events -> Invitation\".\"id\" AS \"institutions.users.events.Invitation.id\",
   \"institutions -> users -> events -> Invitation\".\"eventId\" AS \"institutions.users.events.Invitation.eventId\",
   \"institutions -> users -> events -> Invitation\".\"userId\" AS \"institutions.users.events.Invitation.userId\",
   \"institutions -> users -> events -> Invitation\".\"institutionId\" AS \"institutions.users.events.Invitation.institutionId\",
   \"institutions -> users -> events -> Invitation\".\"paid\" AS \"institutions.users.events.Invitation.paid\",
   \"institutions -> users -> events -> Invitation\".\"status\" AS \"institutions.users.events.Invitation.status\",
   \"institutions -> users -> events -> Invitation\".\"createdAt\" AS \"institutions.users.events.Invitation.createdAt\",
   \"institutions -> users -> events -> Invitation\".\"updatedAt\" AS \"institutions.users.events.Invitation.updatedAt\" 
FROM
   \"Events\" AS \"Event\" 
   LEFT OUTER JOIN
      (
( \"Invitations\" AS \"institutions -> Invitation\" 
         INNER JOIN
            \"Institutions\" AS \"institutions\" 
            ON \"institutions\".\"id\" = \"institutions -> Invitation\".\"institutionId\") 
         INNER JOIN
            (
               \"UserDetails\" AS \"institutions -> users -> UserDetails\" 
               INNER JOIN
                  \"Users\" AS \"institutions -> users\" 
                  ON \"institutions -> users\".\"id\" = \"institutions -> users -> UserDetails\".\"userId\"
            )
            ON \"institutions\".\"id\" = \"institutions -> users -> UserDetails\".\"institutionId\" 
            AND 
            (
               \"institutions -> users\".\"deletedAt\" IS NULL 
               AND \"events\".\"id\" = '1'
            )
         LEFT OUTER JOIN
            (
               \"Invitations\" AS \"institutions -> users -> events -> Invitation\" 
               INNER JOIN
                  \"Events\" AS \"institutions -> users -> events\" 
                  ON \"institutions -> users -> events\".\"id\" = \"institutions -> users -> events -> Invitation\".\"eventId\"
            )
            ON \"institutions -> users\".\"id\" = \"institutions -> users -> events -> Invitation\".\"userId\" 
      )
      ON \"Event\".\"id\" = \"institutions -> Invitation\".\"eventId\" 
      AND 
      (
         \"institutions\".\"deletedAt\" IS NULL
      )
WHERE
   \"Event\".\"id\" = '1';

Answer 1

错误是由于内部连接，要删除它，请执行以下操作：

1. 将include中的where子句全部取出到out。
2. 使用最外层的subQuery: false

将查询更改为：

const event = await Event.findByPk(req.params.eventId, {
        subQuery: false,
        where: {
           ['$users.events.id$']: req.params.eventId
        },
        include: [
          {
            model: Institution,
            as: 'institutions',
            attributes: ['id', 'name'],
            through: {
              model: Invitation
            },
            include: [
              {
                model: User,
                as: 'users',
                attributes: ['id', 'name'],
                through: {
                  model: UserDetails,
                  attributes: []
                },
                include: [
                  {
                    model: Event,
                    as: 'events',
                    attributes: [],
                    through: {
                      model: Invitation,
                      attributes: []
                    }
                  }
                ]
              }
            ]
          }
        ]
      });

希望它能成功！

为我工作:)

Answer 2

通常，多到多关联是通过单独的查询获得的，这就是为什么不能为父级的子关联添加条件子句的原因。

您可以使用include将条件移动到model: Event，但是这样就可以根据条件获得具有过滤事件的所有用户。我想这不是你想要达到的目标。

您可以尝试将sequelize.literal包含在用户级别where上的过滤事件的子查询中，如下所示：

model: User,
                as: 'users',
                where: sequelize.where(sequelize.literal('(EXISTS (SELECT 1 FROM Events e join Invitation i on <here is the rest of subquery>))'), true)