How can I write findAll in sequelize

Question

I have 2 models.
The table zen show all transactions of user.
User can send money to another user.
I want to join nickname to zen table.

zen.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const zen = sequelize.define('zen', {
    from_user_id: DataTypes.BIGINT,
    to_user_id: DataTypes.BIGINT,
    zen_price: DataTypes.INTEGER,
  }, {
    underscored: true,
  });
  zen.associate = function(models) {
    // associations can be defined here
  };
  return zen;
};

+--------------+------------+-----------+
| from_user_id | to_user_id |     price |
+--------------+------------+-----------+
|            1 |          2 |       200 |
|            1 |          3 |       200 |
|            1 |          4 |       200 |
|            1 |          5 |       200 |
|            2 |          1 |       200 |
|            2 |          3 |       200 |
|            2 |          4 |       200 |
|            2 |          5 |       200 |
|            3 |          1 |       200 |
|            3 |          2 |       200 |
|            3 |          4 |       200 |
|            3 |          5 |       200 |
|            4 |          1 |       200 |
|            4 |          2 |       200 |
|            4 |          3 |       200 |
|            4 |          5 |       200 |
|            5 |          1 |       200 |
|            5 |          2 |       200 |
|            5 |          3 |       200 |
|            5 |          4 |       200 |
+--------------+------------+-----------+

user.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const user = sequelize.define('user', {
    nickname: DataTypes.STRING,
  }, {
    underscored: true,
    paranoid: true
  });
  user.associate = function(models) {
    // associations can be defined here
  };
  return user;
};

+----+----------+
| id | nickname |
+----+----------+
|  1 | umi      |
|  2 | minato   |
|  3 | haruto   |
|  4 | rikuto   |
|  5 | yamato   |
+----+----------+

I've wrote sql like this

select g.from_user_id, t1.nickname, g.to_user_id, t2.nickname from zens g inner join users t1 on g.from_user_id = t1.id inner join users t2 on g.to_user_id = t2.id;

The result is

mysql> select g.from_user_id, t1.nickname, g.to_user_id, t2.nickname from zens g inner join users t1 on g.from_user_id = t1.id inner join users t2 on g.to_user_id = t2.id;
+--------------+----------+------------+----------+
| from_user_id | nickname | to_user_id | nickname |
+--------------+----------+------------+----------+
|            1 | umi      |          2 | minato   |
|            1 | umi      |          3 | haruto   |
|            1 | umi      |          4 | rikuto   |
|            1 | umi      |          5 | yamato   |
|            2 | minato   |          1 | umi      |
|            2 | minato   |          3 | haruto   |
|            2 | minato   |          4 | rikuto   |
|            2 | minato   |          5 | yamato   |
|            3 | haruto   |          1 | umi      |
|            3 | haruto   |          2 | minato   |
|            3 | haruto   |          4 | rikuto   |
|            3 | haruto   |          5 | yamato   |
|            4 | rikuto   |          1 | umi      |
|            4 | rikuto   |          2 | minato   |
|            4 | rikuto   |          3 | haruto   |
|            4 | rikuto   |          5 | yamato   |
|            5 | yamato   |          1 | umi      |
|            5 | yamato   |          2 | minato   |
|            5 | yamato   |          3 | haruto   |
|            5 | yamato   |          4 | rikuto   |
+--------------+----------+------------+----------+
20 rows in set (0.00 sec)

How can I write association and include in the function findAll

exports.findAll = (req, res) => {
  console.log('**** findAll ****')

  Zens.findAll({
  })
  .then(data => {
    res.send(data);
  })
  .catch(err => {
    res.status(500).send({
      message:
        err.message || "Some error occurred while retrieving tutorials."
    });
  });

}

Thank you in advance.

Anatoly Anatoly · Accepted Answer · 2020-05-10T09:22:35

You should add to associations from zen to user with different aliases (so sequelize and you can distinguish them from each other).

zen.associate = function(models) {
    zen.belongsTo(models.user, { foreignKey: 'from_user_id', as: 'FromUser' })
    zen.belongsTo(models.user, { foreignKey: 'to_user_id', as: 'ToUser' })
};

Indicate the include option in findAll with associations mentioned above:

Zens.findAll({
     include: [{
       model: db.user,
       as: 'FromUser'
     }, {
       model: db.user,
       as: 'ToUser'
     }]
  })

This way you'll get zen objects with two object props FromUser and ToUser.

If you wish to get a certain afields from associated models you can indicate the attributes option in each included model:

Zens.findAll({
     attributes: ['zen_price'],
     include: [{
       model: db.user,
       as: 'FromUser',
       attributes: ['nickname']
     }, {
       model: db.user,
       as: 'ToUser',
       attributes: ['nickname']
     }]
  })

How can I write findAll in sequelize

1 Answers