Sequelize join with group by

Question

Sequelize join with group by

What I want to do would be best illustrated by a small example.

Say I have the following tables:

Table Item

id
name

Table Review

id
rating
itemId

Obviously there are many Reviews to one Item.

How do I write a query which returns the following two columns:

An item's name (Item.name)
The sum of all an item's ratings (Sequelize.fn('sum', sequelize.col('rating')))

I'd like to use the ORM.

node.js
orm
sequelize.js

Answer 1

Try to use this:

Review.belongsTo(Item, {foreignKey:'itemId', as : 'reviews'});
Item.hasMany(Review, {foreignkey:'id'});

Item.findAll({attributes:[['id', 'id'],['name', 'name'],[Sequelize.fn('SUM', 'rating'), 'total']], include:[{model:Review, as : 'reviews', required:true}], group:['id'], order:[Sequelize.fn('SUM', 'rating'), 'DESC']}).success(function(result){
    // return array;
    // total     -----> result[i].total
    // item name -----> result[i].name
})