Average of grouped data in column using Sequelize

3
votes

I'm trying to find the average of a 'ratings' column for all the data that has the same 'VenueId'. I have it working with raw code but need to adapt it in Sequelize. The working raw code is 

SELECT venueId, AVG(rating) AS average FROM reviews GROUP BY VenueId

The code I've tried is 

Review.findAll({
    order: [[Sequelize.fn('AVG', Sequelize.col('rating'))]]
}).then(function(Venues) {})

I'm getting an error:

Executing (default): SELECT id, review, rating, createdAt, updatedAt, VenueId FROM Reviews AS Review ORDER BY max(rating); Unhandled rejection SequelizeDatabaseError: UNKNOWN_CODE_PLEASE_REPORT: Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query.

Any ideas?

2
averagesequelize.js

2 Answers

5
votes

This might need some tweaking as I don't know your models, but I suggest you try the following:

Review.findAll({
   attributes: ['venueId', [models.sequelize.fn('AVG', models.sequelize.col('venue_id')), 'venueIdCount']],
   group: 'venue_id'
   order: [[models.sequelize.fn('AVG', models.sequelize.col('venue_id')), 'DESC']]
}).then(function() {
   //Do something
}})

Important to notice: When I use "venueId" I mean the name of the attribute on your model, and when I use "venue_id" I mean the name of the column in your database table. They might be the same or different, so feel free to tweak it.

3
votes

You must do this

Review.findAll({
    attributes: ['venueId', [models.sequelize.fn('AVG', 
    models.sequelize.col('rating')), 'ratingAvg']],
    group: ['venueId'],
    order: [[models.sequelize.fn('AVG', models.sequelize.col('rating')), 'DESC']]
}).then(function() {
    //Do something
}})

Or this with async/await

async(() => {
    return await(Review.findAll({
        attributes: ['venueId', [models.sequelize.fn('AVG', 
        models.sequelize.col('rating')), 'ratingAvg']],
        group: ['venueId'],
        order: [[models.sequelize.fn('AVG', models.sequelize.col('rating')), 'DESC']]}))
})