如何在Node.js Sequelize中判断特定用户是否点赞帖子

我是Node.js新手，正在使用PostgreSQL和Express搭建后端，已定义以下数据模型及关联关系：

UserModel.js #

const UserModel = sequelize.define(
  "user",
  {
    ID: {
      type: Sequelize.INTEGER,
      autoIncrement: true,
      allowNull: false,
      unique: true,
    },
    UID: { type: Sequelize.STRING(10), allowNull: false, primaryKey: true },
    name: { type: Sequelize.STRING, allowNull: false },
    contact: { type: Sequelize.STRING(10), allowNull: false },
    dob: { type: Sequelize.DATEONLY, allowNull: false },
    profile_image: { type: Sequelize.STRING, allowNull: false },
});

UserModel.hasMany(PostModel, { foreignKey: "userId" });
PostModel.belongsTo(UserModel, { foreignKey: "userId", allowNull: false });
UserModel.hasMany(LikeModel, { foreignKey: "userId" });
LikeModel.belongsTo(UserModel, { foreignKey: "userId", allowNull: false });

PostModel.js #

const PostModel = sequelize.define("posts", {
  ID: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    unique: true,
  },
  PID: { type: Sequelize.STRING(15), allowNull: false, primaryKey: true },
  title: { type: Sequelize.TEXT, allowNull: false },
  description: { type: Sequelize.TEXT, allowNull: false },
});

PostModel.hasMany(LikeModel, { foreignKey: "postId" });
LikeModel.belongsTo(PostModel, { foreignKey: "postId", allowNull: false });

LikeModel.js #

const LikeModel = sequelize.define("likes", {
  ID: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true,
  },
  userId: { type: Sequelize.STRING(10), allowNull: false },
});

目前我能获取包含用户详情和点赞列表的帖子，但希望为每个帖子添加isLiked: true/false字段（标识特定用户是否点赞该帖子），同时将点赞列表替换为点赞总数likes。当前查询代码如下：

const tlsPosts = await PostModel.findAll({
    order: [["createdAt", "DESC"]],
    limit: 25,
    offset: index === 1 ? 0 : index * 25,
    where: { userId: "TMLKSH" },
    include: [
      {
        model: UserModel
      },
      { model: LikeModel },
    ],
  });

当前返回结果 #

{
    "posts": [
      {
        "ID": 2,
        "desc": "wertyuiop[]",
        "createdAt": "2022-12-31T05:14:25.000Z",
        "updatedAt": "2022-12-31T05:29:48.133Z",
        "userId": "ID-567hjk",
        "likes": [
          {
            "ID": 3,
            "userId": "ID-567hjk"
          },
          {
            "ID": 3,
            "userId": "ID-898ghj"
          }
        ]
      }
    ]
  }

期望返回结果 #

{
  "posts": [
    {
      "ID": 2,
      "desc": "wertyuiop[]",
      "createdAt": "2022-12-31T05:14:25.000Z",
      "updatedAt": "2022-12-31T05:29:48.133Z",
      "userId": "ID-567hjk",
      "isLiked": true,
      "likes": 2
    }
  ]
}

我尝试了多种方法但未成功，不想使用循环处理，寻求最优实现方案。

可以通过Sequelize的聚合函数和子查询直接在数据库层面完成计算，避免代码层循环处理，具体实现如下：

// 替换为你要判断的目标用户ID
const targetUserId = "ID-567hjk";

const tlsPosts = await PostModel.findAll({
  order: [["createdAt", "DESC"]],
  limit: 25,
  offset: index === 1 ? 0 : index * 25,
  where: { userId: "TMLKSH" },
  include: [
    { model: UserModel }
  ],
  attributes: {
    include: [
      // 统计当前帖子的点赞总数
      [sequelize.fn('COUNT', sequelize.col('likes.ID')), 'likes'],
      // 通过子查询判断目标用户是否点赞该帖子
      [
        sequelize.literal(`EXISTS(SELECT 1 FROM likes WHERE likes.postId = posts.PID AND likes.userId = '${targetUserId}')`),
        'isLiked'
      ]
    ]
  },
  // 按帖子和关联用户的主键分组，确保聚合结果正确
  group: ['posts.PID', 'user.UID'],
  // 确保关联用户的属性被包含在分组逻辑中
  includeIgnoreAttributes: false
});

代码说明 #

1. 点赞总数计算：使用COUNT聚合函数统计每个帖子关联的点赞记录数，映射为likes字段。
2. isLiked字段实现：通过EXISTS子查询检查目标用户是否存在于当前帖子的点赞列表，返回布尔值作为isLiked字段。
3. 分组设置：必须按帖子主键（posts.PID）和关联用户主键（user.UID）分组，否则聚合函数会导致数据丢失。
4. 移除点赞列表的直接关联：不再直接包含LikeModel，而是通过聚合和子查询获取所需字段，减少不必要的数据传输。

安全提示 #

如果targetUserId是动态用户输入，为避免SQL注入风险，建议使用参数绑定方式：

[
  sequelize.literal(`EXISTS(SELECT 1 FROM likes WHERE likes.postId = posts.PID AND likes.userId = ?)`, targetUserId),
  'isLiked'
]

内容的提问来源于stack exchange，提问作者Abhishek Karad