4

say I have two models: Post and Comment, now I can use Post.findAll() to get all posts, but I also need the comment count of each post, I can make a loop and use post.countComments() to get the count, but is it possible to do that in one query? thanks

IndexOutOfDevelopersException
  • 1,364
  • 7
  • 15
  • 28
wong2
  • 34,358
  • 48
  • 134
  • 179
  • Possible duplicate of [sequelize subquery as field](http://stackoverflow.com/questions/28286811/sequelize-subquery-as-field) – jjbskir Jan 05 '17 at 16:19
  • Another possible duplicate - http://stackoverflow.com/questions/29869077/how-to-count-a-group-by-query-in-nodejs-sequelize – jjbskir Jan 05 '17 at 17:45

2 Answers2

1

It is very much possible with findAndCountAll method provided by the sequelize

Once you make a query by Post.findAndCountAll({include: [{model: Comment, as: 'comments'}]})

by doing post.comments.length you can get the count of comments of each post.

In case, if you would like to find the count of a single post use

Post.findAndCount({where: {id: postId}}, include:[{model: Comments, as: 'comments'}]}) which returns {count: <#comments>, rows: [<Post>]}

Richie
  • 4,404
  • 2
  • 17
  • 22
0

You can do something like this:

var attributes = Object.keys(Post.attributes);
var sequelize = Post.sequelize;

attributes.push([sequelize.literal('(SELECT COUNT(*) FROM "Comments" where "Comments"."postId" = "Post"."postId")'), 'commentsCount']);

var query = {
  attributes: attributes,
  include: [{model: Comment}]
}
Post.findAndCountAll(query)
  .then(function(posts){
    ...
  })
Edudjr
  • 1,676
  • 18
  • 28