mysql - Sequelize 查询在使用 include 时仅返回一行

Question

上下文: 我遇到了这个问题，我正在使用 sequilize 进行查询，即使我有多个字段对应于查询，它也只会返回一个具有一个位置的数组。

这是我参与的两个模型

这是我的 group.js 模型

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;
}

这是我的 Movement.js 模型

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;
}

这是我的 查询 (您将看到我正在执行 INNER JOIN 来对 运动 表的字段进行 SUM)

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" });
    }
});

问题 是它只返回预期数组的一个位置:

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

应该 返回 2 个位置

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

这是 sequilize 给我的查询:

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

我想你需要添加一个适当的 group by 子句如下 -

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