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?
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"]
]
},
});