DEV Community

topninja
topninja

Posted on

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'
        });
    };
Enter fullscreen mode Exit fullscreen mode

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'
    });
  };
Enter fullscreen mode Exit fullscreen mode

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'
        });
    };
Enter fullscreen mode Exit fullscreen mode

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'],
            ]
        },
    }
});
Enter fullscreen mode Exit fullscreen mode

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,
...
Enter fullscreen mode Exit fullscreen mode

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

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs