Sequelize js how to get average (aggregate) of associated model

Question

I am trying to get average rating of an associated Model "Rating" of Model "User" using 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);
})

I get correct response when trying directly from "Rating" Model:

[ { rating: '3.5000000000000000' } ]

However, when trying to do the same through the association of "User", I get separate values instead of getting average.

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

I had to add "seller_rating.id" and "user.id" in group as sequelize was throwing error otherwise.

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

Following are the models for User and Rating User:

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

Rating:

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

This solution should work for you:

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

Answer 2

Solution that worked for me:

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

result:

{
  "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"
}