How to add sequelize function attributes in include Model?

How are you. I am using Sequelize on my backend, I faced a basic problem.

Friend Model

const Friend = sequelize.define('Friend', {
        id: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        user_id: {
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        invite_user_id: DataTypes.INTEGER,
        status: DataTypes.TINYINT  //0: send invite, 1: accepted, 2: refuse, 3: cancel
    },{
        paranoid: true,
    });
    Friend.associate = function (models) {
        this.hasOne(models.Users, {
            foreignKey: 'id',
            as: 'incoming_user',
            sourceKey: 'user_id'
        });
        this.hasOne(models.Users, {
            foreignKey: 'id',
            as: 'invite_user',
            sourceKey: 'invite_user_id'
        });
    };

User Model

const Users = sequelize.define('Users', {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER
    },
    user_id: DataTypes.STRING,
    user_name: DataTypes.STRING,
    nick_name: DataTypes.STRING,
    email: DataTypes.STRING,
  }, {
    timestamps: false
  });
  Users.associate = function (models) {
    this.hasMany(models.GameHistory);
    this.belongsTo(models.Friend, {
      foreignKey: 'user_id'
    });
  };

Game History Model

const GameHistory = sequelize.define('GameHistory', {
        id: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        room_id: DataTypes.STRING,
        user_id: DataTypes.INTEGER,
        speed: DataTypes.INTEGER, //words per minute
        accuracy: DataTypes.DECIMAL(10,2)
    });
    game_history.associate = function (models) {
        this.belongsTo(models.Users, {
            foreignKey: 'user_id'
        });
    };

I want to get friends connected with me, so

let my_friends = await Friend.findAll({
    where: {
        status: 1,
        user_id: req.user.id  // this is my user id
    },
    attributes: ['invite_user_id'],
    include: {
        model: Users,
        as: 'invite_user',
        include: {
            model: GameHistory,
            attributes: []
        },
        attributes: {
            include: [   
                [Sequelize.fn('ROUND', Sequelize.fn('AVG', Sequelize.col('speed')), 2), 'avg_speed'],
                [Sequelize.fn('AVG', Sequelize.col('accuracy')), 'avg_accuracy'],
            ]
        },
    }
});

I hope this returns All friends connected with me. But this only returns only 1 friend, not all. Without Round, Avg sequelize function, it returns all correctly but when I add these attributes, it returns only 1. I added separate: true but it returns the last one user data only.

{
  "invite_user_id": 11,
  "invite_user": {}    //// ----> user data empty here
},
{ 
  "invite_user_id": 8,
  "invite_user": {
      {
         "user_avatar": "http://111.jpg",
         "id": 8,
         "user_id": "ninja",
         "user_name": "xyz",
         "nick_name": null,
...

When GameHistory has some data then this returns correctly, without empty GameHistory, it returns 1 friend only.

So My question is How to use sequelize function (Round, Avg) with empty table.

Reference:
https://stackoverflow.com/questions/73440280/how-to-add-sequelize-function-attributes-in-include-model

Blog

How to add sequelize function attributes in include Model?

topninja

Join Our Newsletter. No Spam, Only the good stuff.

Related