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'})
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));
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"
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With