sequelize 中的关联没有按预期工作

2022-01-19 00:00:00 postgresql javascript sequelize.js

我正在尝试输出嵌套关系，其中

I am attempting to output a nested relation where

Cat.hasMany(腿)

Cat.hasMany(legs)

Leg.belongsTo(猫)

Leg.belongsTo(cat)

Leg.hasOne(爪子)

Leg.hasOne(paw)

paw.hasMany(腿)

paw.hasMany(leg)

这是我的猫模型:

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 表时，我的代码正在输出此内容

Currently My code is outputting this when i query the Cat Table

[
    {
        "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 也存在.更多类似的东西:

However I would like the pawType from the paws table to also be present when listing everything from the cat table. Something more along the lines of this:

[
    {
        "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"
            }
        ]
    }
]

此外，这是我用来检索 Cats 的查询.

Additionally, Here is the query I am using to retrieve the Cats.

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 命令

And the full SQL command

   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";' },

推荐答案

有很多问题.我会尝试逐步解决这些问题.

There are many issues. I'll try to address them incrementally.

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

1) Models By default, if you do not declare a primaryKey, then sequelize automatically adds an id column for you. Thus legId isn't a useful column.

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

Furthermore, if you associate a model, the foreignKey reference is added for you, thus pawId shouldn't be declared.

因此 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 中给了我以下列:

The above gives me the following columns in pgAdmin:

2) 关联

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

The following association doesn't make sense, and should cause an error:

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

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

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

Each Leg should have one Paw, and thus I suggest the following:

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) 预加载

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

When eager loading nested associations, you have to include them. You should also use as alias that matches your model associations:

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

使用整个设置和上面的查询，我得到:

Using this entire setup and the above query, I obtain:

[
  {
    "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 关系(也许您已经通过爪子连接了猫?)如下:

Because this is obviously a practice setup, you could modify Paw to be a belongsToMany relation (perhaps you have conjoined cats by the paw?) as follows:

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

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

This would be the correct way to implement what you initially tried to with

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

相关文章