'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 |
|---|
