Count related models and limit using Sequelize-CodePudding

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?

CodePudding user response：

To use limit and offset options properly you need to use a subquery with Sequelize.literal instead of Sequelize.fn and remove the include and group options at all.

const elements = await ElementsModel.findAll({
      limit: 10,
      offset: 0,
      attributes: {
        include: [
  // correct a table name and fields if needed
  [Sequelize.literal("(SELECT COUNT(*) FROM Related where Related.elementId=Elements.id)", "related_total"]
      ]
      },
    });