在 Sequelize 中查询关联表 [英] Querying association tables in Sequelize

问题描述

我有两个表(users 和 games)由一个关联表(game_players)连接，创建了多对多关系:

I have two tables (users and games) joined by an association table (game_players), creating a many-to-many relationship:

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 还有一些额外的用于链接特定数据的列:

In addition to the foreign keys user_id and game_id, game_players has a few extra columns for link-specific data:

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
});

假设我想获取一个游戏并急切地加载活跃玩家.这是我的第一次尝试:

Suppose I want to fetch a game and eagerly load active players. This was my first effort:

db.models.Game.findAll({
    include: [{
        model: db.models.User,
        as: 'players',
        where: { 'game_player.isDisabled': false }
    }]
}).nodeify(cb);

这会生成以下SQL，抛出错误Column games.game_player.isDisabled does not exist:

This generates the following SQL, which throws the error 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 代码以正确查询此列?

Clearly Sequelize is wrapping my constraint alias with incorrect quotes: 'players'.'game_player.isdisabled' should be 'players.game_player'.isdisabled. How can I revise my Sequelize code above to correctly query this column?

推荐答案

我知道了，但只能通过手动浏览存储库已关闭的票证并遇到 #4880.

I got it, but only through manually browsing the repository's closed tickets and coming upon #4880.

使用无法立即使用的连接表列的子句可以用 $ 包装.老实说，我不明白它的魔力，因为我发誓我没有看到任何有关它的文档.修改我上面的查询实现了我想要的:

Clauses using joined table columns that don't work out of the box can be wrapped in $. I honestly don't understand its magic, because I swear I don't see any documentation for it. Modifying my query above achieved what I wanted:

db.models.Game.findAll({
    include: [{
        model: db.models.User,
        as: 'players',
        where: { '$players.game_player.is_disabled$': false }
    }]
}).nodeify(cb);

这篇关于在 Sequelize 中查询关联表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！