Associations in sequelize not working as intended

Question

I am attempting to output a nested relation where

Cat.hasMany(legs)

Leg.belongsTo(cat)

Leg.hasOne(paw)

paw.hasMany(leg)

Here is my 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;
};

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

Here is my 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;
};

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

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

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

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

This is the error that is returning,

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

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

Additionally, Here is the query I am using to retrieve the Cats. `return Cat.findAll({ include: [{ model: Leg, as: 'legs', }], })` — sar agnew, Apr 14 '18 at 22:39

vapurrmaid · Accepted Answer · 2018-04-15T11:38:33.127

4

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

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.

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

Thus Legs.js should be modified to:

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
}

The above gives me the following columns in pgAdmin:

2) Associations

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

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) Foreign Keys

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

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

4) Eager Loading

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

Extra

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)

edited Apr 15 '18 at 11:38

answered Apr 15 '18 at 00:13

vapurrmaid

2,287
2
14
30

I tried this, however, it returns an error. I updated my question to include the query as well as the error that is being returned. Thank you in advance. – sar agnew Apr 15 '18 at 01:56
1

I've done a massive update to my answer. In the future, do not update your question based off of an answer right away. Instead use the comment section and let the user that answered update their answer. Only update the question based on comments made directly to the question or as a final resort (say the answer user becomes non-responsive). This is because modifying a question completely invalidates existing answers. – vapurrmaid Apr 15 '18 at 11:40
Hey thanks for your update. This helps me understand associations much better. I do have one question regarding paw and leg through join. Lets say i had a list of paws in a table and i wanted my 'legs' table to be able to add the id of any paw within the paw table, how would i go about doing that. Similar to this: `"legs": [ { "id": 20, "catId": 1, "pawId": 3 "paw": { "id": 3, "pawType": null, "createdAt": "2018-04-15T11:22:59.906Z", "updatedAt": "2018-04-15T11:22:59.906Z", } } ]` – sar agnew Apr 15 '18 at 16:15
No problem at all. Sorry for the additional commentary, just trying to help explain how the site works (most of what I said is available in the [help center](https://stackoverflow.com/help)). If you're only adding a single paw `id` per `leg`, then you have a **one-to-one** relationship. Use `hasOne` and `belongsTo`. Otherwise I'm not sure I entirely get the question unfortunately. – vapurrmaid Apr 15 '18 at 16:19
In your edited comment, you have `pawId` and `paw.id` - it's redundant. I think you may find it helpful to read very carefully the **target** and **source** in [the associations documentation](http://docs.sequelizejs.com/manual/tutorial/associations.html). In a `source.hasOne(target)` relationship, the `target` gets a column `sourceId`. **HasOne associations are associations where the foreign key for the one-to-one relation exists on the target model.**. You wouldn't want a `pawId` column on `Leg`, AND a `LegId` column on `Paw` in a *one-to-one* relationship. – vapurrmaid Apr 15 '18 at 16:37
It seems as thou in the example above, the leg `id` and the `pawId` have to match in order for `paw` to be included in the output, however is there a way to specify `pawId` in the `legs` table so that the output includes whatever is found in the `paws` table that matches the `pawId` residing in the `legs` table? – sar agnew Apr 15 '18 at 16:44
I will say that I can't figure out a use-case for that, but it is possible. you'll need to do something like: `Leg.findAll({ include: [{ model: Paw, where: { id: // use this } }] })` – vapurrmaid Apr 15 '18 at 16:48
1

Ahh ok great. Thanks so much for the help! – sar agnew Apr 15 '18 at 17:00
No problem. Just be aware that if you delete the `Paw` with `id` you're referencing in that column, you have to also delete it there as well (hence why it's not very practical to do this). Upvoted question, thanks for accepting my answer. If you found it useful, please upvote the answer as well. Hope everything helped. – vapurrmaid Apr 15 '18 at 17:05

Associations in sequelize not working as intended

1 Answers1

Linked