Get Count of Sequelize BelongsToMany Association

Question

I have two models, project and user that are associated with each other in a few ways.

First, a project is owned by a user with the following association:

Project.belongsTo(models.User, { as: 'Owner', foreignKey: 'userId' });

Secondly, a project and user are associated by way of a user liking a project.

Project.belongsToMany(models.User, { as: 'UserLikes', through: models.ProjectLikes, foreignKey: 'projectId' });

User.belongsToMany(models.Project, { as: 'LikedProjects', through: models.ProjectLikes, foreignKey: 'userId' });

Thirdly, a project and user can also be associated by way of a user being part of a project via a Role.

Project.belongsToMany(models.User, { as: 'Users', through: models.ProjectRoles, foreignKey: 'projectId' });

User.belongsToMany(models.Project, { as: 'Roles', through: models.ProjectRoles, foreignKey: 'userId' });

What I am trying to achieve is to find all projects and along with them return the associated "like" count. However I am really struggling to figure out how to do this.

I have read that I should be able to do something like:

models.Project.findAll({
    limit: 5,
    attributes: ['Project.*', [models.sequelize.fn('COUNT', models.sequelize.col('UserLikes.id')), 'totalLikes']],
    include: [ { model: models.User, as: 'UserLikes' }],
    group: [ models.Project.rawAttributes.id, models.sequelize.col('UserLikes.id') ]
  })
  .then(function (projects) {
    return res.json(projects);
  });

But I get a missing FROM-clause entry for table "UserLikes error.

I've tried changing the associations on both sides to hasMany using a specific joining-table model and then doing:

models.Project.findAll({
    limit: 5,
    attributes: ['Project.*', 'ProjectLikes.id', [models.sequelize.fn('COUNT', models.sequelize.col('ProjectLikes.id')), 'totalLikes']],
    include: [ { model: models.ProjectLikes }],
    group: [ models.Project.rawAttributes.id ]
  })
  .then(function (projects) {
    return res.json(projects);
  });

But I get the same error, or errors about Ids being ambiguous or errors about Project.id needing to be in the GROUP BY clause (even though it is?!).

I am tearing my hair out trying to figure out something that should be really straight forward... I am almost considering switching back to MongoDB!

Please help... Thanks!

Answer 1

I have the same problem for belongsToMany, but I managed to solve it for hasMany. You have to put attributes: [] for models.ProjectLikes (inside the include) and for fixing the limit bug you have to put subQuery: false in the findAll options.