Counting in Many-To-Many Relations Sequelize

Question

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": "xyz@gmail.com",
        "followingCount": "1",         <------ I want this to be 3
        "following": [
            {
                "id": "484202b0-a6d9-416d-a8e2-6681deffa3d1",
                "userName": "uqwheuo",
                "email": "uqwheuo@gmail.com"
            },
            {
                "id": "56c8d9b0-f5c6-4b2e-b32c-be6363294614",
                "userName": "aiwhroanc",
                "email": "aiwhroanc@gmail.com"
            },
            {
                "id": "9a3e4074-c7a0-414e-8df4-cf448fbaf5fe",
                "userName": "iehaocja",
                "email": "iehaocja@gmail.com"
            }
        ]
    }

I am not able to count in Joint Table..

Emma · Accepted Answer · 2022-01-18T18:34:13.437

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: [],
      }
    },
  ],
});

Yeah this method works. But what if I have to get both `followers` and `followings` ? adding two `[Sequelize.literal()]....` statement for both followers and followings doesn't seem to give proper values.... one gives correct and another gives incorrect — rickster, Jan 18 '22 at 15:59
I am having same problem with `"PostLikes"` join table... where it contains `userId` and `postId`... aliasing `likes` to both field in association.. I want to count number of likes on some post with all the liked users and other post details.... here is query just for count... ```[sequelize.literal(`COUNT("likes->PostLikes"."postId") OVER (PARTITION BY "likes->PostLikes"."userId")`), 'likesCount'],``` not giving required output.. — rickster, Jan 19 '22 at 05:44
it is hard to say without seeing the model definitions and associations. If you still have the issue, you can post the new question. — Emma, Jan 19 '22 at 15:17
I have posted the question here : https://stackoverflow.com/questions/70781543/count-in-many-to-many-relationship-sequelize — rickster, Jan 20 '22 at 06:11

Counting in Many-To-Many Relations Sequelize

1 Answers1

Linked