Counting join with sequelize with multiple includes

Question

I'm using the count method of mysql in sequelize but when I have multiple nested includes the count attribute makes the request returns less results. The problem is the same with the sum method.

I tried with the attributes group and duplicate but none of them resolved the problem.

I'm using Sequelize 4.33.4 but I tried with Sequelize 5 and the result is still the same.

Model :

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

The request only returns the first proposition (the one with the lowest propositionId) when I have the 2 attributes. When I remove them it sends me all the results.

What SQL is generated using `findOne()` ? Sequelize has some placement flaws when it generates the `LIMIT` keyword. — KenOn10, Aug 05 '19 at 13:54
@KenOn10 It generates a where clause with a limit 1 : https://pastebin.com/pfdUkWaK — Simon, Aug 05 '19 at 14:06
But the count is on the included tables, not the first one so even with a findAll the result is still the same — Simon, Aug 05 '19 at 14:15

score 8 · Answer 1 · answered Aug 05 '19 at 19:11

a) Your results suggest that the nested includes should be outer joins .... so adding required: false (shown below) might fix this.

b) Sequelize might be mixing up the two associations from Proposition to User. You might try adding an alias to the belongsTo, e.g. models.Proposition.belongsTo(models.User, {as: 'whatever', foreignKey: 'userId'}); and use the alias in the find:

...
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 **/
 ...

Hope this helps....

The generated SQL has some oddities. 1) There is no GROUP BY statement... so your summary operations aren't working. You might try moving`group: []` to the Challenge level, and specify all non-summary fields in the query. 2) Also, despite your `attributes: [],` there are a bunch of fields from `Propositions->Fan->like. You might try moving all attributes to the Challenge level, too (which may require that you qualify field names). — KenOn10, Aug 06 '19 at 15:13

Counting join with sequelize with multiple includes

1 Answers1