CODEWITHSUNDEEP
javascriptnode.jssequelize.js
David Costa
David Costa

Reputation: 165

Sequelize: How associate two fields from a table another table

I have the following table settings from my sequelize.

const Accounts = sequelize.define('Accounts', {
  name: DataTypes.STRING,
});

const Transfers = sequelize.define('Transfers', {
  value: {
    type: DataTypes.DECIMAL(10, 2),
    defaultValue: 0,
  },
  accountIdFrom: DataTypes.INTEGER,
  accountIdTo: DataTypes.INTEGER,
});

Transfers.belongsTo(Accounts, { foreignKey: 'accountIdFrom' });
Transfers.belongsTo(Accounts, { foreignKey: 'accountIdTo' });

const data = await Transfers.findAll({
  include: [{ model: Accounts }]
});

Return: 

{
  "id": 1,
  "value": "10.00",
  "accountIdFrom": 1,
  "accountIdTo": 2,
  "Account": {
    "id": 2,
    "name": "Banco Neon",
  }
}

I tried to use the association setting this way, but the sequelize always associates with just one field, and I want it to either show for both fields. accountIdFrom andacountIdTo.

The expected return should be something like this, but, it is not working:

{
  "id": 2,
  "value": "10.00",
  "accountIdFrom": 2,
  "accountIdTo": 1,
  "AccountFrom": {
    "id": 2,
    "name": "Bank Two",
  },
  "AccountTo": {
    "id": 1,
    "name": "Bank One",
  }
}

Upvotes: 4

Views: 2827

Answers (2)

David Costa
David Costa

Reputation: 165

I finded the solution with help of @Ellebkey. Follow bellow the code.

create-transfers.js 

module.exports = {
  up: (queryInterface, Sequelize) => (
    queryInterface.createTable('Transfers', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      value: {
        type: Sequelize.DECIMAL(10, 2),
        defaultValue: 0,
      },
      accountFromId: {
        type: Sequelize.INTEGER,
      },
      accountToId: {
        type: Sequelize.INTEGER,
      },
    })
      .then(() => (
        queryInterface.addConstraint('Transfers', ['accountFromId'], {
          type: 'foreign key',
          name: 'fk_transfers_account_from',
          references: { // Required field
            table: 'Accounts',
            field: 'id',
          },
          onDelete: 'cascade',
          onUpdate: 'no action',
        })
      ))
      .then(() => (
        queryInterface.addConstraint('Transfers', ['accountToId'], {
          type: 'foreign key',
          name: 'fk_transfers_account_to',
          references: { // Required field
            table: 'Accounts',
            field: 'id',
          },
          onDelete: 'cascade',
          onUpdate: 'no action',
        })
      ))
  ),
  down: queryInterface => queryInterface.dropTable('Transfers'),
};

model/transfers.js 

module.exports = (sequelize, DataTypes) => {
    const Transfers = sequelize.define('Transfers', {
        value: {
            type: DataTypes.DECIMAL(10, 2),
            defaultValue: 0,
        },
        accountFromId: DataTypes.INTEGER,
        accountToId: DataTypes.INTEGER,
    }, {});
    Transfers.associate = ({ Accounts }) => {
        Transfers.belongsTo(Accounts, { as: 'AccountFrom' });
        Transfers.belongsTo(Accounts, { as: 'AccountTo' });
    };
    return Transfers;
};

controller/transfers.js 

const data = await Transfers.find({
    where: {},
    include: [{
        model: database.Accounts,
        as: 'AccountFrom',
    }, {
        model: database.Accounts,
        as: 'AccountTo',
    }],
});

Upvotes: 0

Ellebkey
Ellebkey

Reputation: 2301

You have to use as: instead of foreignKey:

Transfers.belongsTo(Accounts, { as: 'accountFrom', onDelete: 'cascade', onUpdate: 'no action' });
Transfers.belongsTo(Accounts, { as: 'accountTo', onDelete: 'cascade', onUpdate: 'no action' });

This will give you on your Accounts model the columns accountFromId and accountToId. So when you need to include the models you're going to do it like this:

Transfers.find( {
  where: {},
  include: [{
    model: db.Accounts,
    as: 'accountFrom'
  },{
    model: db.Accounts,
    as: 'accountTo'
  }]
})

Upvotes: 2

Related Questions