JOIN SubQuery in @nest/sequelize

I would like to get a rating for each movie

This query works as it should

SELECT *
FROM movie
LEFT JOIN (
    SELECT "movieId", avg(score), count("userId")
    FROM rating
    GROUP BY rating."movieId"
) AS rating ON rating."movieId" = movie.id 
WHERE movie.title ilike '%Titanic%'
LIMIT 10

The code I have now

return this.movieRepo.findAndCountAll({
  where: {
    [Op.or]: {
      title: { [Op.iLike]: '%' + query + '%' },
      author: { [Op.iLike]: '%' + query + '%' },
      publisher: { [Op.iLike]: '%' + query + '%' },
    },
  },
  limit,
  offset: (page - 1) * limit,
});

It seems you need to use two subqueries in attributes option using Sequelize.literal:

return this.movieRepo.findAndCountAll({
  attributes: {
    include: [
      [Sequelize.literal('(SELECT AVG(rating.score) FROM rating WHERE rating."movieId"=movie.id)'), 'avg_score'],
      [Sequelize.literal('(SELECT count(rating."userId") FROM rating WHERE rating."movieId"=movie.id)'), 'cnt_user']
    ]
  },
  where: {
    [Op.or]: {
      title: { [Op.iLike]: '%' + query + '%' },
      author: { [Op.iLike]: '%' + query + '%' },
      publisher: { [Op.iLike]: '%' + query + '%' },
    },
  },
  limit,
  offset: (page - 1) * limit,
});