Sequelize associate and query for this mysql query

Question

I just want to excute this simple mysql query in sequelize but It's really confusing..

SELECT *,
       (SELECT COUNT(*)
         FROM  user_review_like
        WHERE  user_review_like.review_id = review.id)
FROM  reviews

so I tried like this.

const reviewList = await Review.findAndCountAll({
  attributes: ['*', fn('COUNT', col('user_review_like'))],
  include: [
    {
      model: UserReviewLike,
      attributes: [],
    },
  ],
  where,
  raw: true,
  offset: offset,
  limit: 5,
  order,
});

but It said 'userReviewLike is not associated to Review!'

Model 'review':

static associate(db) {
  db.Review.belongsTo(db.User); --> for matching review writer and users
  db.Review.belongsToMany(db.User, {through: 'user_review_like', foreignKey: 'review_id'});
}

Model 'user':

static associate(db) {
  db.User.hasMany(db.Review);
  db.User.belongsToMany(db.Review, {through: 'user_review_like', foreignKey: 'user_id'});
}

Model 'userReviewLike':

{
    user_id: {
    },
    review_id: {
    },
  },
static associate(db) {
db.UserReviewLike.belongsTo(db.User, {
  foreignKey: 'user_id'
});
db.UserReviewLike.belongsTo(db.Review, {
  foreignKey: 'review_id'
});

}

It seems much more convenient cuz I'm setting where and order dynamically, but I'm confused about join query.

any advice please

Thank you

Answer 1

To be able to indicate a junction model in include explicitly you also should define an association from Review to UserReviewLike explicitly:

db.Review.hasMany(db.UserReviewLike, { foreignKey: 'review_id' })

And I suppose to be able to use COUNT as a subquery you need to use Sequelize.literal, otherwise you will get something like SELECT *, COUNT(*) ...

const reviewList = await Review.findAndCountAll({
  attributes: { include: [
    [literal('(SELECT COUNT(*)
         FROM  user_review_like
        WHERE  user_review_like.review_id = review.id)'), 'like_count']
  ]
  },
  where,
  raw: true,
  offset: offset,
  limit: 5,
  order,
});