我正在使用Sequelize进行节点项目,但是我在以Sequelize方式转换查询时遇到了一些麻烦 .
场景:我有两个表(User和Event),通过UserEvent表具有n:m关系 . 在UserEvent表中有is_creator属性;我需要的是一个具有事件名称,事件ID和is_creator字段的对象 .
例:
[{
id: 1,
name: "Event",
is_creator: true
}]
这是查询:
SELECT `Event`.`id`,`Event`.`name` as `Name`, `UserEvents`.`is_creator` AS `is_creator` FROM `Events` AS `Event` INNER JOIN (`UserEvents` INNER JOIN `Users` AS `Users` ON `Users`.`id` = `UserEvents`.`UserId`) ON `Event`.`id` = `UserEvents`.`EventId` AND `Users`.`id` = 1;
不幸的是,我无法将其“翻译”为Sequelize .
实际代码:
var queryOptions: FindOptions = {
include: [
{
model: db.User,
where: {
id: user.id
}
}
],
};
执行查询:
SELECT `Event`.`id`, `Event`.`uuid`, `Event`.`image`, `Event`.`name`, `Event`.`start_at`, `Event`.`end_at`, `Event`.`location`, `Event`.`description`, `Event`.`createdAt`, `Event`.`updatedAt`, `Users`.`id` AS `Users.id`, `Users`.`uuid` AS `Users.uuid`, `Users`.`email` AS `Users.email`, `Users`.`password` AS `Users.password`, `Users`.`firstName` AS `Users.firstName`, `Users`.`lastName` AS `Users.lastName`, `Users`.`activationCode` AS `Users.activationCode`, `Users`.`resetCode` AS `Users.resetCode`, `Users`.`active` AS `Users.active`, `Users`.`dob` AS `Users.dob`, `Users`.`image` AS `Users.image`, `Users`.`createdAt` AS `Users.createdAt`, `Users`.`updatedAt` AS `Users.updatedAt`, `Users.UserEvent`.`uuid` AS `Users.UserEvent.uuid`, `Users.UserEvent`.`is_creator` AS `Users.UserEvent.is_creator`, `Users.UserEvent`.`createdAt` AS `Users.UserEvent.createdAt`, `Users.UserEvent`.`updatedAt` AS `Users.UserEvent.updatedAt`, `Users.UserEvent`.`UserId` AS `Users.UserEvent.UserId`, `Users.UserEvent`.`EventId` AS `Users.UserEvent.EventId` FROM `Events` AS `Event` INNER JOIN (`UserEvents` AS `Users.UserEvent` INNER JOIN `Users` AS `Users` ON `Users`.`id` = `Users.UserEvent`.`UserId`) ON `Event`.`id` = `Users.UserEvent`.`EventId` AND `Users`.`id` = 1;
在此先感谢您的合作!
1 回答
是否要限制查询返回的列数?
您可以查看
attributes
选项 . 您可以指定所需的表字段 .例如,如果您想要用户的id,email和firstName,则可以通过这种方式指定它 .
您可以类似地为UserEvents表添加attributes选项 .
PS:我目前没有足够的声誉来增加对问题的评论 . 否则我会那样做的 .