我有两个表( users
和 games
)通过关联表( game_players
)连接,创建了多对多关系:
models.Game.belongsToMany(models.User, { through: models.GamePlayer, as: 'players' });
models.User.belongsToMany(models.Game, { through: models.GamePlayer, foreignKey: 'user_id' });
除了外键 user_id
和 game_id
之外, game_players
还有一些额外的列用于特定于链接的数据:
sequelize.define('game_player', {
isReady: {
defaultValue: false,
type: Sequelize.BOOLEAN,
field: 'is_ready'
},
isDisabled: {
defaultValue: false,
type: Sequelize.BOOLEAN,
field: 'is_disabled'
},
powerPreferences: {
type: Sequelize.TEXT,
field: 'power_preferences'
},
power: {
type: Sequelize.STRING(2),
defaultValue: '?'
}
}, {
underscored: true
});
假设我想要一个游戏,并急切地加载活跃的玩家 . 这是我的第一次努力:
db.models.Game.findAll({
include: [{
model: db.models.User,
as: 'players',
where: { 'game_player.isDisabled': false }
}]
}).nodeify(cb);
这会生成以下SQL,它会抛出错误 Column players.game_player.isDisabled does not exist
:
SELECT "game"."id",
"game"."name",
"game"."description",
"game"."variant",
"game"."status",
"game"."move_clock" AS "moveClock",
"game"."retreat_clock" AS "retreatClock",
"game"."adjust_clock" AS "adjustClock",
"game"."max_players" AS "maxPlayers",
"game"."created_at",
"game"."updated_at",
"game"."gm_id",
"game"."current_phase_id",
"players"."id" AS "players.id",
"players"."email" AS "players.email",
"players"."temp_email" AS "players.tempEmail",
"players"."password" AS "players.password",
"players"."password_salt" AS "players.passwordSalt",
"players"."action_count" AS "players.actionCount",
"players"."failed_action_count" AS "players.failedActionCount",
"players"."created_at" AS "players.created_at",
"players"."updated_at" AS "players.updated_at",
"players.game_player"."is_ready" AS
"players.game_player.isReady",
"players.game_player"."is_disabled" AS
"players.game_player.isDisabled",
"players.game_player"."power_preferences" AS
"players.game_player.powerPreferences",
"players.game_player"."power" AS "players.game_player.power",
"players.game_player"."created_at" AS
"players.game_player.created_at",
"players.game_player"."updated_at" AS
"players.game_player.updated_at",
"players.game_player"."game_id" AS
"players.game_player.game_id",
"players.game_player"."user_id" AS
"players.game_player.user_id"
FROM "games" AS "game"
INNER JOIN ("game_players" AS "players.game_player"
INNER JOIN "users" AS "players"
ON "players"."id" = "players.game_player"."user_id")
ON "game"."id" = "players.game_player"."game_id"
AND "players"."game_player.isdisabled" = false;
很明显,Sequelize用不正确的引号包装我的约束别名:'players' . 'game_player.isdisabled'应该是'players.game_player'.isdisabled . 如何修改上面的Sequelize代码以正确查询此列?
2 回答
我得到了它,但只能通过手动浏览存储库的已关闭的票据然后来到#4880 .
使用无法使用的连接表列的子句可以包装在
$
中 . 老实说,我没有看到任何文件 . 修改我上面的查询实现了我想要的:您的查询应该在具有'where'条件的连接表上,然后您应该使用'include'子句来包含其他两个模型,如下所示: