javascript - sequelize 中的关联未按预期工作

Question

我正在尝试输出一个嵌套关系

Cat.hasMany(腿)

Leg.belongsTo(猫)

Leg.hasOne(爪子)

爪子.hasMany(腿)

这是我的猫模型:

module.exports = (sequelize, DataTypes) => {
  const Cat = sequelize.define('Cat', {
    userId: {
      type: DataTypes.STRING,
    },
  }, {});

  Cat.associate = function (models) {
    Cat.hasMany(models.Leg, {
      foreignKey: 'catId',
      as: 'legs',
    });
  };
  return Cat;
};

我的腿模型:

module.exports = (sequelize, DataTypes) => {
  const Leg = sequelize.define('Leg', {
    originalValue: DataTypes.JSON,
    newValue: DataTypes.JSON,
    legId: DataTypes.INTEGER,
    objectId: DataTypes.INTEGER,
    pawId: DataTypes.INTEGER,
  }, {});

  Leg.associate = function (models) {
    Leg.belongsTo(models.Cat, {
      foreignKey: 'LegId',
      onDelete: 'CASCADE',
    });
    Leg.hasOne(models.Paw, {
      foreignKey: 'pawId',
    });
  };
  return Leg;
};

这是我的爪子模型

module.exports = (sequelize, DataTypes) => {
  const Paw = sequelize.define('Paw', {
    pawType: DataTypes.STRING,
  }, {});
  Paw.associate = function (models) {
    Paw.hasMany(models.Leg, {
      foreignKey: 'pawId',
      as: 'paws',
    });
  };
  return Paw;
};

目前我的代码在查询 Cat 表时输出这个

[
    {
        "id": 1,
        "userId": "2wdfs",
        "createdAt": "2018-04-14T20:12:47.112Z",
        "updatedAt": "2018-04-14T20:12:47.112Z",
        "legs": [
            {
                "id": 1,
                "catId": 1,
                "pawId": 1,
                "createdAt": "2018-04-14T20:12:54.500Z",
                "updatedAt": "2018-04-14T20:12:54.500Z"
            }
        ]
    }
]

但是，当列出 cat 表中的所有内容时，我希望 paws 表中的 pawType 也存在。更多类似的内容:

[
    {
        "id": 1,
        "userId": "2wdfs",
        "createdAt": "2018-04-14T20:12:47.112Z",
        "updatedAt": "2018-04-14T20:12:47.112Z",
        "legs": [
            {
                "id": 1,
                "catId": 1,
                "paws" : [
                   {
                    "id": 1,
                    "pawType": "cute"
                   }
                ]
                "createdAt": "2018-04-14T20:12:54.500Z",
                "updatedAt": "2018-04-14T20:12:54.500Z"
            }
        ]
    }
]

此外，这是我用来检索猫的查询。

return Cat.findAll({ include: [{ model: Leg, as: 'legs',include [{model: Paw,}], }], })

这是返回的错误，

{ SequelizeDatabaseError: column legs->Paw.pawId does not exist
{ error: column legs->Paw.pawId does not exist

以及完整的 SQL 命令

   sql: 'SELECT "Cat"."id", "Cat"."userId", "Cat"."createdAt", "Cat"."updatedAt", "legs"."id" AS "legs.id", "legs"."originalValue" AS "legs.originalValue", "legs"."newValue" AS "legs.newValue", "legs"."catId" AS "legs.catId", "legs"."objectId" AS "legs.objectId", "legs"."pawId" AS "legs.pawId", "legs"."createdAt" AS "legs.createdAt", "legs"."updatedAt" AS "legs.updatedAt", "legs->Paw"."id" AS "legs.Paw.id", "legs->Paw"."paw" AS "legs.Paw.paw", "legs->Paw"."pawId" AS "legs.Paw.pawId", "legs->Paw"."createdAt" AS "legs.Paw.createdAt", "legs->Paw"."updatedAt" AS "legs.Paw.updatedAt" FROM "Cats" AS "Cat" LEFT OUTER JOIN "Legs" AS "legs" ON "Cat"."id" = "legs"."catId" LEFT OUTER JOIN "Paws" AS "legs->Paw" ON "legs"."id" = "legs->Paw"."pawId";' },

Answer 1

有很多问题。我将尝试逐步解决这些问题。

1) 模型 默认情况下，如果您没有声明primaryKey，sequelize 会自动为您添加一个id 列。因此 legId 不是有用的列。

此外，如果您关联模型，则会为您添加foreignKey 引用，因此不应声明pawId。

因此Legs.js应该修改为:

module.exports = (sequelize, DataTypes) => {
  var Leg = sequelize.define('Leg', {
    originalValue: DataTypes.JSON,
    newValue: DataTypes.JSON,
    objectId: DataTypes.INTEGER // not entirely sure what this is 
  })
  Leg.associate = function (models) {
    // associations
  }
  return Leg
}

上面给出了 pgAdmin 中的以下列:

2) 协会

以下关联没有意义，应该导致错误:

Leg.hasOne(Paw)
Paw.hasMany(Leg)

Unhandled rejection Error: Cyclic dependency found. Legs is dependent of itself.
Dependency chain: Legs -> Paws => Legs

每条Leg 都应该有一个Paw，因此我建议如下:

Leg.associate = function (models) {
  // Leg.belongsTo(models.Cat)
  Leg.hasOne(models.Paw, {
    foreignKey: 'pawId',
    as: 'paw'
  })
}

Paw.associate = function (models) {
  Paw.belongsTo(models.Leg, {
    as: 'leg' // note this changed to make more sense
    foreignKey: 'pawId'
  })
}

3) 外键

Leg.belongsTo(models.Cat, {
  foreignKey: 'catId', // this should match
  onDelete: 'CASCADE'
})

Cat.hasMany(models.Leg, {
  foreignKey: 'catId', // this should match
  as: 'legs'
})

4) 预先加载

急切加载嵌套关联时，您必须包含它们。您还应该使用与您的模型关联相匹配的 as 别名:

Cat.findAll({
  include: [{
    model: Leg,
    as: 'legs', // Cat.legs 
    include: [{
      model: Paw,
      as: 'paw' // Leg.paw instead of Leg.pawId
    }]
  }]
})

使用整个设置和上述查询，我​​获得:

[
  {
    "id": 1,
    "userId": "1",
    "createdAt": "2018-04-15T11:22:59.888Z",
    "updatedAt": "2018-04-15T11:22:59.888Z",
    "legs": [
      {
        "id": 1,
        "originalValue": null,
        "newValue": null,
        "objectId": null,
        "createdAt": "2018-04-15T11:22:59.901Z",
        "updatedAt": "2018-04-15T11:22:59.901Z",
        "catId": 1,
        "paw": {
          "id": 1,
          "pawType": null,
          "createdAt": "2018-04-15T11:22:59.906Z",
          "updatedAt": "2018-04-15T11:22:59.906Z",
          "pawId": 1
        }
      }
    ]
  }
]

额外

因为这显然是一个练习设置，所以您可以将 Paw 修改为 belongsToMany 关系(也许您已经用爪子连接了猫？)，如下所示:

Paw.associate = function (models) {
  Paw.belongsToMany(models.Leg, {
    foreignKey: 'pawId',
    through: 'PawLegs  // a through join table MUST be defined
  })
}

这将是实现您最初尝试使用的方法的正确方法

Leg.hasOne(paw)
paw.hasMany(leg)