The title is rewritten as: Use Sequelize to count the number of rows in related tables

Question

Using sequelize and mySQL, I have two tables: User and Post.

The relationship between the two tables is M:N

db.User.belongsToMany(db.Post, { through: "Likes", as: "Liked" });
db.Post.belongsToMany(db.User, { through: "Likes", as: "Likers" });

What I want is to get all the liker ids and the number of likers of the post.

I know you can get all likers like this.

const post = await Post.findOne({
  where: { id: postId },
  attributes: ["id", "title", "imageUrl"],
  include: [{
    model: User,
    as: "Likers",
    attributes: ["id"],
    through: { attributes: [] },
  }]
})

// result
{
  "id": 36,
  "title": "test",
  "imageUrl": "하늘이_1644886996449.jpg",
  "Likers": [
    {
      "id": 13
    },
    {
      "id": 16
    }
  ]
}

Also, I also know that I can get the number of likes this way.

const post = await Post.findOne({
  where: { id: postId },
  attributes: ["id", "title", "imageUrl"],
  include: [{
    model: User,
    as: "Likers",
    attributes: [[sequelize.fn("COUNT", "id"), "likersCount"]],
  }]
})

// result
{
  "id": 36,
  "title": "test",
  "imageUrl": "하늘이_1644886996449.jpg",
  "Likers": [
    {
      "likersCount": 2
    }
  ]
}

However, I don't know how to get both of them at the same time. Check the results when I use them both.

{
  model: User,
  as: "Likers",
  attributes: ["id", [sequelize.fn("COUNT", "id"), "likersCount"]],
  through: { attributes: [] },
}

// result
"Likers": [
  {
    "id": 13,
    "likersCount": 2
  }
]

It only shows one liker (id: 13) It should show another liker (id: 16).

What is the problem?

Answer 1

It only shows one because COUNT is an aggregate function that groups records for counting. So the only way to get both is to use a subquery, count the number of records in the join table, and get the records on the other side of the M:N relationship at the same time.

const post = await Post.findOne({
  where: { id: postId },
  attributes: ["id", "title", "imageUrl", 
  // 你可能需要更正表和字段的名称
  [Sequelize.literal('(SELECT COUNT(*) FROM Likes where Likes.postId=Post.id)'), 'LikeCount']],
  include: [{
    model: User,
    as: "Likers",
    attributes: ["id"],
    through: { attributes: [] },
  }]
})