How to get isLiked the post or not in NodeJs Sequelize

Question

I'm new to NodeJs.

I'm using PostgreSql and Express to create backend on node. The Data model's are as follow.

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 });

LikesModel.js

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

I'm managing the relation between User table, post table and like table. I'm able to get all the posts with user details and total likes. But I want to get a key as well which tells whether the a specific user liked that post or not isLiked : true/false for each post.

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

What I'm getting

{
    "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"
          }
        ]
      }
    ]
  }

What I want

{
  "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
    }
  ]
}

Thanks

I tried a lot of things but they didn't work, like I can use loop as well. But I want an optimized approach and code for the same.

Answer 1

const tlsPosts = await PostModel.findAll({
  where: { },
  attributes: {
    include: [
      [
        // Note this sequlize is an instance of connection.
        // so import from connection config not from Sequlize library
        sequelize.literal(
          `( SELECT COUNT(*) FROM likes 
            WHERE likes."postId" = posts."ID"
            AND likes."userId" = '${userId}'
          )`
        ),
        'isLiked'
      ], [
        Sequelize.fn('COUNT', Sequelize.col('likes.postId')),
        'totalLikes'
      ],
    ],
  },
  include: [
    {
      model: LikeModel,
      attributes: [],
      required: false,
      right: false
    },
  ],
  offset: index === 1 ? 0 : index * 25,
  order: [["createdAt", "DESC"]],
  group: [
    'posts.ID',
    "posts.PID",
  ],
  subQuery: false,
  limit: 25,
});

result = {
  "data": [
    {
      "ID": 4,
      "PID": "PID2-UID-2",
      "title": "Demo Title2 For UID1",
      "description": "Demo Description",
      "createdAt": "2023-02-28T04:50:33.548Z",
      "updatedAt": "2023-02-28T04:50:33.548Z",
      "userId": 2,
      "totalLikes": "2",
      "isLiked": "0"
    }, {
      "ID": 1,
      "PID": "PID1-UID-1",
      "title": "Demo Title1 For UID1",
      "description": "Demo Description",
      "createdAt": "2023-02-28T04:50:33.548Z",
      "updatedAt": "2023-02-28T04:50:33.548Z",
      "userId": 1,
      "totalLikes": "3",
      "isLiked": "0"
    },
  ]
}

Value of isLiked would be 1 or 0 for true, false