Sequelize associations are not functioning optimally as expected

Question

Sequelize associations are not functioning optimally as expected

Trying to display a nested relationship:

Cat.hasMany(legs)

Leg.belongsTo(cat)

Leg.hasOne(paw)

paw.hasMany(leg)

This is the Cat Model:

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

The Legs Model:

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

The Paw model

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

When querying the Cat Table, the code currently displays:

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

I want to include the pawType from the paws table like 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"
                }
            ]
        }
    ]

Query used to retrieve Cats:

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

Error received:

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

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

javascript postgresql sequelize.js

Answer 1

Answer №1

There are several issues that need to be addressed one by one.

1) Models When you do not specify a primaryKey, sequelize automatically adds an id column for you. Therefore, the legId column is redundant and unnecessary.

Additionally, when associating models, the foreignKey reference is automatically added for you, so there is no need to declare the pawId column.

Therefore, the modified version of the Legs.js file should look like this:

module.exports = (sequelize, DataTypes) => {
  var Leg = sequelize.define('Leg', {
    originalValue: DataTypes.JSON,
    newValue: DataTypes.JSON,
    objectId: DataTypes.INTEGER // purpose unclear 
  })
  Leg.associate = function (models) {
    // associations
  }
  return Leg
}

The modifications above result in the following columns being displayed in pgAdmin: https://i.stack.imgur.com/VNsLc.png

2) Associations

The association below will cause an error due to cyclic dependency:

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

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

For each Leg to have one Paw, the following adjustments are suggested:

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' // revised for clarity
    foreignKey: 'pawId'
  })
}

3) Foreign Keys

Leg.belongsTo(models.Cat, {
  foreignKey: 'catId', // ensure consistency
  onDelete: 'CASCADE'
})

Cat.hasMany(models.Leg, {
  foreignKey: 'catId', // ensure consistency
  as: 'legs'
})

4) Eager Loading

When eager loading nested associations, remember to use the include option and provide aliases using as:

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

With this setup and query, the output will be as follows:

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

Extra

If you want to model a many-to-many relationship between Paw and Leg, you can define it as shown below:

Paw.associate = function (models) {
  Paw.belongsToMany(models.Leg, {
    foreignKey: 'pawId',
    through: 'PawLegs' // ensure you define the through join table
  })
}

This approach is a more appropriate way of implementing what was previously attempted with the code snippet:

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

Answer 2

There are several issues that need to be addressed one by one.

1) Models When you do not specify a primaryKey, sequelize automatically adds an id column for you. Therefore, the legId column is redundant and unnecessary.

Additionally, when associating models, the foreignKey reference is automatically added for you, so there is no need to declare the pawId column.

Therefore, the modified version of the Legs.js file should look like this:

module.exports = (sequelize, DataTypes) => {
  var Leg = sequelize.define('Leg', {
    originalValue: DataTypes.JSON,
    newValue: DataTypes.JSON,
    objectId: DataTypes.INTEGER // purpose unclear 
  })
  Leg.associate = function (models) {
    // associations
  }
  return Leg
}

The modifications above result in the following columns being displayed in pgAdmin: https://i.stack.imgur.com/VNsLc.png

2) Associations

The association below will cause an error due to cyclic dependency:

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

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

For each Leg to have one Paw, the following adjustments are suggested:

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' // revised for clarity
    foreignKey: 'pawId'
  })
}

3) Foreign Keys

Leg.belongsTo(models.Cat, {
  foreignKey: 'catId', // ensure consistency
  onDelete: 'CASCADE'
})

Cat.hasMany(models.Leg, {
  foreignKey: 'catId', // ensure consistency
  as: 'legs'
})

4) Eager Loading

When eager loading nested associations, remember to use the include option and provide aliases using as:

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

With this setup and query, the output will be as follows:

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

Extra

If you want to model a many-to-many relationship between Paw and Leg, you can define it as shown below:

Paw.associate = function (models) {
  Paw.belongsToMany(models.Leg, {
    foreignKey: 'pawId',
    through: 'PawLegs' // ensure you define the through join table
  })
}

This approach is a more appropriate way of implementing what was previously attempted with the code snippet:

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

Sequelize associations are not functioning optimally as expected

Answer №1

Similar questions

Issues have been encountered with activating checkboxes on Internet Explorer while utilizing ASP.NET CheckBox controls

Navigating through the maze of ES6 imports and dealing with the complexities

mentioning a JSON key that includes a period

Conceal YouTube upon opening any model or light box

Are there any creative methods for structuring Node.js Alexa code efficiently?

Looking for assistance in showcasing information retrieved from an external API

The material-ui library always registers Event.ctrlKey as true

JavaScript truthy values referring to numbers

axios encountering a 400 bad request error during the get request

What's the best way to launch multiple Node.js services within a single Docker Compose configuration?

The entry '0-0' already exists for the key 'local_part', please enter a unique value

Organizing JSON keys based on their values using Typescript

Comparing AngularJS $interpolate with $filter

Update the URL and content in Django dynamically

Encountering an issue when implementing a conditional check within the .map() function utilizing ES6 syntax

Create dynamic connections between animated sprites using Three.js

Circular dependency in Typescript/Javascript: Attempting to extend a class with an undefined value will result in an error,

<use> - SVG: Circles with different stroke properties

Utilize the v-if directive with nested object properties for dynamic conditional rendering

What is the best way to create a dynamic icon using React and TypeScript?