Counting in Many-To-Many Relations Sequelize

I am trying to count number of Followers and Followings of a user. as followingCount and followerCount

User Model

User.init(
    {
      id: {
        allowNull: false,
        primaryKey: true,
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
      },
      email: {
        type: DataTypes.STRING,
      },
      password: {
        type: DataTypes.STRING,
      },
    }
  );

static associate(models) {
      // Follow relationship
      this.belongsToMany(models.User, {
        through: 'UserFollow',
        foreignKey: 'followerId',
        as: 'following',
      });

      // Follow relationship
      this.belongsToMany(models.User, {
        through: 'UserFollow',
        foreignKey: 'followeeId',
        as: 'follower',
      });
    }

Where UserFollow is a Joint Table with columns followeeId and followerId.

My current approach for finding number of followings is something like this :

const user = await User.findOne({
      where: {
        id,
      },
      attributes: [
        'id',
        'userName',
        'email',
        [sequelize.fn('COUNT', sequelize.col('following->UserFollow.followeeId')), 'followingCount'],
      ],
      include: [
        {
          model: User,
          as: 'following',
          attributes: ['id', 'userName', 'email'],
          through: {
            attributes: [],
          },
        },
      ],
      group: ['User.id', 'following.id'],
    });

    return user;

And Output getting is like this:

Here I am getting followingCount as 1... but it should be 3.

"data": {
        "id": "1af4b9ea-7c58-486f-a37a-e46461487b06",
        "userName": "xyz",
        "email": "[email protected]",
        "followingCount": "1",         <------ I want this to be 3
        "following": [
            {
                "id": "484202b0-a6d9-416d-a8e2-6681deffa3d1",
                "userName": "uqwheuo",
                "email": "[email protected]"
            },
            {
                "id": "56c8d9b0-f5c6-4b2e-b32c-be6363294614",
                "userName": "aiwhroanc",
                "email": "[email protected]"
            },
            {
                "id": "9a3e4074-c7a0-414e-8df4-cf448fbaf5fe",
                "userName": "iehaocja",
                "email": "[email protected]"
            }
        ]
    }

I am not able to count in Joint Table..

Solution 1:

The reason that you are getting followingCount: 1 is that you group by following.id (followeeId). It only counts unique followeeId which is always 1.

Although, if you take out following.id from group, the SQL doesn't work any more. It will crash with "a column must appear in GROUP BY clause...". This is a common issue in Postgres and this link (https://stackoverflow.com/a/19602031/2956135) explains the topic well in detail.

To solve your question, instead of using group, you can use COUNT OVER (PARTITION BY).

const user = await User.findOne({
  where: {
    id,
  },
  attributes: [
    'id',
    'userName',
    'email',
    [Sequelize.literal('COUNT("following->UserFollow"."followeeId") OVER (PARTITION BY "User"."id")'), 'followingCount']
  ],
  include: [
    {
      model: User,
      as: 'following',
      attributes: ['id', 'userName', 'email'],
      through: {
        attributes: [],
      }
    },
  ],
});

======================================================

Update:

The original query only fetch "following" relationship. In order to fetch followers of this user, you first need to add "follower" association.

Then, since 2 associations is added, we need to add 1 more partition by column to count exactly the followers or followees.

const followeeIdCol = '"following->UserFollow"."followeeId"';
const followerIdCol = '"follower->UserFollow"."followerId"';

const user = await User.findOne({
  where: {
    id,
  },
  attributes: [
    'id',
    'userName',
    'email',
    // Note that the COUNT column and partition by column is reversed.
    [Sequelize.literal(`COUNT(${followeeIdCol}) OVER (PARTITION BY "Users"."id", ${followerIdCol})`), 'followingCount'],
    [Sequelize.literal(`COUNT(${followerIdCol}) OVER (PARTITION BY "Users"."id", ${followeeIdCol})`), 'followerCount'],
  ],
  include: [
    {
      model: User,
      as: 'following',
      attributes: ['id', 'userName', 'email'],
      through: {
        attributes: [],
      }
    },
    {
      model: User,
      as: 'follower',       // Add follower association
      attributes: ['id', 'userName', 'email'],
      through: {
        attributes: [],
      }
    },
  ],
});