node.js - Sequelize js 如何获得关联模型的平均值(聚合)

Question

我正在尝试使用 sequelize 获得模型“用户”的关联模型“评级”的平均评级。

sequelize.sync({logging: false}).then(()=>{
  return Model.Rating.findAll({
    attributes: [[Sequelize.fn('avg', Sequelize.col('stars')),'rating']]
  })
}).then(res => {
  res = res.map(r => r.get())
  console.log(res);
})

直接从“评级”模型尝试时，我得到了正确的响应:

[ { rating: '3.5000000000000000' } ]

但是，当尝试通过“用户”的关联执行相同操作时，我会得到单独的值而不是平均值。

sequelize.sync({logging: false}).then(()=>{
  return Model.User.findOne({
    where: {id: 7},
    include : [{
      model: Model.Rating, as: 'seller_rating',
      attributes: [[Sequelize.fn('avg', Sequelize.col('stars')),'rating']]
    }],
    attributes: {
      exclude: ['password']
    },
    group: ['seller_rating.id', 'user.id'],
  })
}).then(res => {
  res = res.get()
  res.seller_rating = res.seller_rating.map(r => r.get())
  console.log(res)
})

我不得不在组中添加“seller_rating.id”和“user.id”，否则 sequelize 会抛出错误。

{
  id: 7,
  email: 'example@gmail.com',
  createdAt: 2020-01-20T09:07:47.101Z,
  updatedAt: 2020-01-21T08:58:52.036Z,
  seller_rating: [
    { rating: '4.0000000000000000' },
    { rating: '3.0000000000000000' }
  ]
}

以下是用户和评级的模型
用户:

let User = sequelize.define('user', {
    email: {type: Sequelize.STRING, unique: true, allowNull: false},
    password : {type: Sequelize.STRING, },
})

评级:

let Rating = sequelize.define('rating', {
    seller_id: {
        type: Sequelize.INTEGER,
        references: {model: User, key: 'id'},
        unique: 'rateObject'
    },
    buyer_id: {
        type: Sequelize.INTEGER,
        references: {model: User, key: 'id'},
        unique: 'rateObject'
    },
    stars: {
        type: Sequelize.INTEGER,
        validate: {
            min: 1,
            max: 5
        },
        allowNull: false
    }
})

Rating.belongsTo(User,{ onDelete: 'cascade', foreignKey: 'seller_id'})
Rating.belongsTo(User,{ onDelete: 'cascade', foreignKey: 'buyer_id'})
User.hasMany(Rating, { foreignKey: 'seller_id', as: 'seller_rating'})
User.hasMany(Rating, { foreignKey: 'buyer_id', as: 'buyer_rating'})

Answer 1

此解决方案应该适合您:

Model.User.findOne({
  where: { id: 7 },
  attributes: [
    [Sequelize.fn('AVG', Sequelize.col('seller_rating.stars')), 'avgRating'],
  ],
  include: [
    {
      model: Model.Rating,
      as: 'seller_rating',
      attributes: [],
    },
  ],
  raw: true,
  group: ['User.id'],
}).then((res) => console.log(res));