Sequelize js如何获取关联模型的平均值（聚合）

Question

我正在尝试使用sequelize获取与模型“用户”相关的模型“评分”的平均评分。

我正在尝试使用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)
})

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

{
  id: 7,
  email: '[e-mail]',
  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'})

- Janak Vashisth

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

- elite0226

---

有没有使用聚合函数的方法可以实现这个？ - elite0107

Answer 2

对我有效的解决方案：

const product = await Product.findOne({
  where: {id: 1},
  include: [
    {
      model: Rating, //including ratings array
      as: 'ratings',
      attributes: [], //but making it empty
    },
  ],
  attributes: {
    include: [ // this adds AVG attribute to others instead of rewriting
      [sequelize.fn('AVG', sequelize.col('ratings.rating')), 'avgRating'],
    ],
  },
  group: ['Product.id'],
});

结果：

{
  "id": 1,
  "name": "product",
  "price": 50,
  "createdAt": "2022-04-21T11:32:56.666Z",
  "updatedAt": "2022-04-21T11:32:56.666Z",
  "categoryId": 1,
  "avgRating": "2.7500000000000000"
}

- Nickers