Sequilize query is returning only one row while using include

Question

Context : I am having this problem were I am doing a query using sequilize an it only return's me an array with one position even though I have more than one field that correspond to the query.

This are my two involved models

This is my group.js model

module.exports = (sequelize, DataTypes) => {
    const Group = sequelize.define('Group', {
        name: DataTypes.STRING,
        limit: DataTypes.STRING,
        user_id: DataTypes.INTEGER
    });

    Group.associate = models => {
        Group.belongsTo(models.User, { foreignKey: 'user_id' });
    };

    Group.associate = models => {
        Group.hasMany(models.Movement, { foreignKey: 'group_id' });
    };

    return Group;
}

This is my movement.js model

module.exports = (sequelize, DataTypes) => {
    const Mov = sequelize.define('Movement', {
        description: DataTypes.STRING,
        value: DataTypes.INTEGER,
        group_id: DataTypes.INTEGER
    });

    Mov.associate = models => {
        Mov.hasOne(models.Group, { foreignKey: 'group_id' });
    };

    return Mov;
}

This is my query (where you will see that I am doing an INNER JOIN to SUM the fields of the Movement table)

router.get('/', verify, async (req, res) => {
    try {
        const group = await Group.findAll({
            attributes: [
                'id',
                'name',
                'limit',
                [sequelize.fn('SUM', sequelize.col('Movements.value')), 'total_spent'],
            ],
            include: [{
                attributes: [], // this is empty because I want to hide the Movement object in this query (if I want to show the object just remove this)
                model: Movement,
                required: true
            }],
            where: {
                user_id: req.userId
            }
        });
        if (group.length === 0) return res.status(400).json({ error: "This user has no groups" })
        res.status(200).json({ groups: group }) //TODO see why this is onyl return one row
    } catch (error) {
        console.log(error)
        res.status(400).json({ Error: "Error while fetching the groups" });
    }
});

Problem is that it only return's one position of the expected array :

{
    "groups": [
        {
            "id": 9,
            "name": "rgrgrg",
            "limit": 3454354,
            "total_spent": "2533"
        }
    ]
}

It should return 2 positions

{
    "groups": [
        {
            "id": 9,
            "name": "rgrgrg",
            "limit": 3454354,
            "total_spent": "2533"
        },
    {
            "id": 9,
            "name": "rgrgrg",
            "limit": 3454354,
            "total_spent": "2533"
        }
    ]
}

This is the query sequilize is giving me:

SELECT `Group`.`id`, `Group`.`name`, `Group`.`limit`, SUM(`Movements`.`value`) AS `total_spent` FROM `Groups` AS `Group` INNER JOIN `Movements` AS `Movements` ON `Group`.`id` = `Movements`.`group_id` WHERE `Group`.`user_id` = 1;

Answer 1

I guess you need to add an appropriate group by clause as follows -

const group = await Group.findAll({
    attributes: [
        'id',
        'name',
        'limit',
        [sequelize.fn('SUM', sequelize.col('Movements.value')), 'total_spent'],
    ],
    include: [{
        attributes: [], // this is empty because I want to hide the Movement object in this query (if I want to show the object just remove this)
        model: Movement,
        required: true
    }],
    where: {
        user_id: req.userId
    },
    group: '`Movements`.`group_id`'
});

Answer 2

Many-to-many "through" table with multiple rows of identical foreign key pairs only returns one result?

I just ran into this bug and added this options to the main query:

{
  raw: true,
  plain: false, 
  nest: true
}

Then you just merge the query.

It's a workaround, but might help someone.