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.
Top comments (0)