'Count related models and limit using Sequelize

I have a working Sequelize query that get models including the count of related models in this way:

    const elements = await ElementsModel.findAll({
      attributes: {
        include: [[Sequelize.fn("COUNT", Sequelize.col("related.id")), "related_total"]]
      },
      include: [
        {
          model: RelatedModel,
          attributes: [],
        }
      ],
      group: ['elements.id']
    });

The query works great, but now I need to include an offset and limit clause for pagination like:

    const elements = await ElementsModel.findAll({
      limit: 10,
      offset: 0,
      attributes: {
        include: [[Sequelize.fn("COUNT", Sequelize.col("related.id")), "related_total"]]
      },
      include: [
        {
          model: RelatedModel,
          attributes: [],
        }
      ],
      group: ['elements.id']
    });

And the query breaks and is not working anymore because of the combination of the included field and the limit clause, generating an invalid query like:

SELECT `elements`.* 
FROM (
  SELECT `elements`.`id`, `elements`.`type`, COUNT(`related`.`coupon_id`) AS `related_total` 
  FROM `elements` AS `elements` GROUP BY `elements`.`id` LIMIT 0, 20
) AS `elements` 
LEFT OUTER JOIN `related` AS `related` ON `elements`.`id` = `related`.`elements_id`;

There is some way to handle this with Sequelize?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source