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