我有两个表(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代码以正确查询此列?
我知道了,但是只能通过手动浏览存储库的关闭票证并遇到#4880来实现。
使用连接的表列而不开箱即用的子句可以包装在里面$
。老实说,我不了解它的魔力,因为我发誓我看不到任何有关它的文档。修改上面的查询可以实现我想要的:
db.models.Game.findAll({
include: [{
model: db.models.User,
as: 'players',
where: { '$players.game_player.is_disabled$': false }
}]
}).nodeify(cb);
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句