Tuesday, 18 April 2017

Sequelize js add total count of associated models

I am trying to modify a query so it also returns a count of elements I want to count the number of "PublicationComments" which relates to a "Publication". Right now the query looks like this:

let query = {
  attributes: {
    exclude: attributesToExclude,
  },
  where: {
    'club_id': this.id,
  },
  order: [['date', 'DESC']],
  include: [
    {
      model: models.PublicationComment,
      association: models.Publication.hasMany(models.PublicationComment, { as: 'lastComments' }),
      order: [['date', 'DESC']],
      limit: 3,
      include: [
        {
          model: models.User, as: 'user',
          attributes: {
            exclude: attributesToExclude,
          },
        }
      ],
    },
    {
      model: models.PublicationLike,
      association: models.Publication.hasMany(models.PublicationLike, { as: 'likes' }),
      attributes: ['userId']
    }
  ]
};

"The result I am trying to obtain would look like this:"

[  { id: '2',
clubId: '26',
teamId: null,
userId: '67',
date: '2017-04-18T11:23:05.628Z',
card: 
 { type: 'status',
   text: 'publication example text',
   video: [Object] },
created_at: '2017-04-18T11:23:05.629Z',
updated_at: '2017-04-18T11:23:05.629Z',
deleted_at: null,
likes: [],
**commentsCount: 4,**
lastComments: [ [Object], [Object], [Object] ] }]

I tried doing add on include attributes the function of count by comments and repeat the association with PublicationComment model without limit parameter (I need the total). Something like this:

 attributes: {
    include: [[sequelize.fn('count', sequelize.col(allComments.id), 'commentsCount']]
    exclude: attributesToExclude,
  },

And add the new associaton inside icludde array:

    {
      model: models.PublicationComment,
      association: models.Publication.hasMany(models.PublicationComment, { as: 'allComments' })         
    }

but after two days of efforts wasted in this I am unable to make it work.

Has anyone any advise, resource or clarification that I could use to advance in this? Thanks.



via mfhevia

No comments:

Post a Comment