如何在include Model中添加sequelize函数属性

Question

你好。我是新来的续集，我面临一个基本问题。

朋友型号

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'
});
};

用户模型

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'
});
};

游戏历史模型

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'
});
};

我想让朋友和我联系，所以

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'],
]
},
}
});

我希望这能回报所有与我有联系的朋友。但这只会返回一个朋友，而不是全部。如果没有Round，Avg sequelize函数，它会正确返回所有属性，但当我添加这些属性时，它只返回1。我添加了separate:true，但它只返回最后一个用户数据。

{
"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,
...

当GameHistory有一些数据时，它会正确返回，没有空的GameHistory，它只返回1个朋友。

所以我的问题是如何在空表中使用sequelize函数(Round，Avg(。

Answer 1

我通过添加组修复了这个问题。

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

感谢