CODEWITHSUNDEEP
node.jsormsequelize.js
wong2
wong2

Reputation: 35750

Sequelizejs: findAll and count associations at the same time

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

Upvotes: 4

Views: 3343

Answers (2)

Richie
Richie

Reputation: 4432

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>]}

Upvotes: 1

Edudjr
Edudjr

Reputation: 1835

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){
    ...
  })

Upvotes: 0

Related Questions