mysql - 计算带有多个包含的 Sequelize 的连接

Question

我在sequelize中使用mysql的count方法，但是当我有多个嵌套包含时，count属性会使请求返回更少的结果。问题与 sum 方法相同。

我尝试使用属性组并复制，但都没有解决问题。

我使用的是 Sequelize 4.33.4，但我尝试使用 Sequelize 5，结果仍然相同。

型号:

// User
module.exports = function(sequelize, DataTypes) {
    var User = sequelize.define('User', {
        userId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        // ...
    }, {
        tableName: 'user'
    });

    User.associate = function (models) {
        models.User.hasMany(models.Proposition, {foreignKey: 'userId'});
        models.User.belongsToMany(models.Proposition, {as: 'Fan', through: 'like', foreignKey: 'userId'});
    };

    return User;
};

// Proposition
module.exports = function(sequelize, DataTypes) {
    const Proposition = sequelize.define('Proposition', {
        propositionId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        photo: DataTypes.STRING(150),
    }, {
        tableName: 'proposition'
    });

    Proposition.associate = function (models) {
        models.Proposition.belongsTo(models.Challenge, {foreignKey: 'challengeId'});
        models.Proposition.belongsTo(models.User, {foreignKey: 'userId'});
        models.Proposition.belongsToMany(models.User, {as: 'Fan', through: 'like', foreignKey: 'propositionId'});
    };

    return Proposition;
};

// Challenge
module.exports = function(sequelize, DataTypes) {
    const Challenge = sequelize.define('Challenge', {
        challengeId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: DataTypes.STRING(100),
        startDate: DataTypes.DATE,
        endDate: DataTypes.DATE,
        image: DataTypes.STRING(150),
        description: DataTypes.STRING(256),
    }, {
        tableName: 'challenge'
    });

    Challenge.associate = function (models) {
        models.Challenge.hasMany(models.Proposition, {foreignKey: 'challengeId'});
    };

    return Challenge;
};

Controller :

exports.getCurrentChallenge = function (req, res) {
    logger.debug('Start getCurrentChallenge');
    models.Challenge.findOne({
        where: {
            startDate: {
                [Op.lte]: new Date()
            },
            endDate: {
                [Op.gte]: new Date()
            }
        },
        include: [{
            model: models.Proposition,
            include: [{
                model: models.User,
                attributes: ['userId', 'firstname', 'lastname', 'profilePicture']
            }, {
                model: models.User,
                as: 'Fan',
                attributes: []
            }],
            required: false,
            attributes: [
                'propositionId',
                [models.sequelize.literal("CONCAT('"+ propositionsPicturesPath + "', `Propositions`.`photo`)"), 'photo'],
                [models.sequelize.literal('COUNT(`Propositions->Fan->like`.`userId`)'), 'likes'], // Show less results
                [models.sequelize.literal('IFNULL(SUM(`Propositions->Fan->like`.`userId` = ' + req.decoded.userId + '), 0)'), 'liked'] // Show less results
            ],
            group: '`Propositions->Fan->like`.`propositionId`',
        }]
    }).then(function (challenge) {
        return res.status(200).send(challenge);
    }, function (err) {
        logger.debug(err.name, err.message);
        return res.sendStatus(500);
    });
};

当我有 2 个属性时，请求仅返回第一个命题(命题 ID 最低的命题)。当我删除它们时，它会向我发送所有结果。

Answer 1

a) 您的结果表明嵌套包含应该是外部联接......因此添加 required: false (如下所示)可能会解决此问题。

b) Sequelize 可能会混淆从 Proposition 到 User 的两个关联。您可以尝试向belongsTo添加别名，例如models.Proposition.belongsTo(models.User, {as: 'whatever',foreignKey: 'userId'}); 并在查找中使用别名:

...
include: [{
            model: models.Proposition,
            include: [{
                model: models.User,
                as: 'whatever',         /**** b) ****/
                required: false,        /**** a) #1 ****/ 
                attributes: ['userId', 'firstname', 'lastname', 'profilePicture']
            }, {
                model: models.User,
                as: 'Fan',
                required: false,        /**** b) #2 ****/ 
                attributes: []
            }],
            required: false,   /** this one is for challenge -> proposition **/
 ...

希望这有帮助......