Sequelize select with two relations to the same entity

user_3921326 0
Viewed 26

I have 2 tables, ItemLegacy :

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('ItemLegacy', {
    id: {
      type: DataTypes.INTEGER(11).UNSIGNED,
      allowNull: false,
      primaryKey: true
    },
    parent: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      defaultValue: 0,
    },
    child: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      defaultValue: 0
    }
  }, {
    tableName: 'ItemLegacy',
    timestamps: false,
    underscored: false
  });
};

and Item :

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('Item', {
    id: {
      type: DataTypes.INTEGER(11).UNSIGNED,
      allowNull: false,
      primaryKey: true
    },
    title: {
      type: DataTypes.STRING(500),
      allowNull: false,
      defaultValue: ''
    },
    code: {
      type: DataTypes.STRING(20),
      allowNull: true
    },
  }, {
    tableName: 'Item',
    timestamps: false,
    underscored: false
  });
};

I also defined two relationships :

db.ccnLegacy.hasOne(db.ccn, { foreignKey: 'id', sourceKey: 'parent' })
db.ccnLegacy.hasOne(db.ccn, { foreignKey: 'id', sourceKey: 'child' })

My question is, I would like to create a select request using sequelize, with a relation for each of the 2 fields parent and child.

I know how to do that with one relation, but how do I do it with 2 ?

My code with only one relation :

db.itemLegacy.findOne({
  raw: true,
  where: { child: idChild },
  include: [
    {
      model: db.item
    },
  ]
})
sequelize.js relationship foreign-keys orm
1 Answers

You simply should indicate aliases for both associations and use them in queries. Second is you used hasOne instead of belongTo because belongTo is used exactly in a case when you go from N to 1 in N:1 relationship.

Associations:

db.ccnLegacy.belongsTo(db.ccn, { foreignKey: 'parent', as: 'Parent' })
db.ccnLegacy.belongsTo(db.ccn, { foreignKey: 'child', as: 'Child' })

Query:

db.itemLegacy.findOne({
  raw: true,
  where: { child: idChild },
  include: [
    {
      model: db.item,
      as: 'Child'
    },
    {
      model: db.item,
      as: 'Parent'
    },
  ]
})
user_13766180
Related