CODEWITHSUNDEEP
sequelize.js
Joshua Ohana
Joshua Ohana

Reputation: 6141

Sequelize - How to setup foreign key and join on it

I have two tables; userPermissions & permissionItems. userPermissions has a permissionItemId as a foreign key, defined as 

CONSTRAINT `fk_userPermissions_permissionItemId`
  FOREIGN KEY (`permissionItemId`)
  REFERENCES `mydb`.`permissionItems` (`permissionItemId`)

Their Sequelize definitions are

const PermissionItem = db.define('permissionItems', {
  permissionItemId: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  }
});


const UserPermission = db.define('userPermissions', {
  userPermissionsId: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  permissionItemId: {
    type: Sequelize.INTEGER,
    references: 'permissionItems',
    referencesKey: 'permissionItemId'
  }
});
UserPermission.hasOne(PermissionItem);

I am then trying to join those two with the and view the results with

UserPermission.findAll({
  include: [{
    model: PermissionItem
  }]
}).then(userPermission => {
  console.log('userPermission', userPermission);
});

My expectation here is to get a list of all UserPermissions and outer left join PermissionItem on permissionItemId. Instead, I receive the below error

Unhandled rejection SequelizeDatabaseError: Unknown column 'permissionItem.userPermissionUserPermissionsId' in 'field list'

Have I constructed the query incorrectly? Or the Sequelize definitions?

I have tried flipping these around and including the reverse, and applying the keys backward, in every combination I could think of, all to no avail

Upvotes: 0

Views: 3996

Answers (3)

Siddharth Sunchu
Siddharth Sunchu

Reputation: 1094

Whenever I want to make an association I usually defined in both the models and assign alias just to better understand and config properly.

    orders.hasMany(models.orderDetails, { 
      as: 'orderDetails',
      foreignKey: 'orderId',
      targetKey: 'id', });

    orderDetails.belongsTo(models.Orders, {
      foreignKey: 'specialOrderId',
      targetKey: 'id',
      as: 'Orders'
    });

Whenever you query to create or find:

    models.orders.create(ObjectToCreate, {
    include: [{ model: models.orderDeatils, as: 'orderDeatils' }],
  });


    models.orders.findAll({ where: { id: 3}}, {
    include: [{ model: models.orderDeatils, as: 'orderDeatils' }],
  });

Upvotes: 1

Joshua Ohana
Joshua Ohana

Reputation: 6141

I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call

UserPermission = db.define('userPermissions', {
  userPermissionsId: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  permissionItemId: {
    type: Sequelize.INTEGER
  }
});
UserPermission.hasMany(PermissionItem, { foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' });

Upvotes: 0

margherita pizza
margherita pizza

Reputation: 7185

In your userPermissions model, Instead of this

UserPermission.hasOne(PermissionItem);

try this

UserPermission.belongsTo(PermissionItem,{foreignKey:'permissionItemId'});

HasOne and BelongsTo insert the association key in different models from each other. HasOne inserts the association key in target model whereas BelongsTo inserts the association key in the source model.

Upvotes: 0

Related Questions