How to count associations in sequelize in postgres database

Question

I have developed APIs in Node.js using sequelize framework. I use Postgres database. Please find below my table structure as below.

category	post	category_post	user_like
id	id	category_id	user_id
category_name	title	post_id	post_id
detail	image_url

I need to develop an API to filter category by provided category id and return category's posts and every post should contain total number of likes in it.

Sample JSON response is as below:

{
    "data":{
        "id":1,
        "category_name":"category 1",
        "posts":[
            {
                "id":1,
                "title":"post 1",
                "total_likes":50,
                "image_url":"image.png"
            }
        ]
    },
    "success":true
}

Please find my code as below:

sequelize.transaction().then(result => {
  return category.findOne({
    where: { "id": request.params.id }, include: [{
      model: post,
      as: "posts",
      attributes: ["id","title","image_url"],
      through: {
        attributes: []
      }
    }],
    attributes: ["id", "category_name"],
  });
}).then(posts => {
  let r = { "data": posts, "success": true }
  response.status(200).json(r)
}).catch(function (err) {
  console.log(err)
});

Kindly guide me on how to get total counts of likes for post. This function doesn't work for me Counting associated entries with Sequelize

Answer 1

There are two junction tables for consideration, namely the category_post and user_like tables. These can be handled using many to many associations. For simplicity, in the code below, I treated the user_like table as if it were a simple child table of the parent table posts, leaving one remaining junction table to consider.

Also, turning on sequelize logging helps a lot with difficult queries.

Here's how I would set up the mappings.

let Category = sequelize.define('categories', {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        category_name: DataTypes.STRING,
        detail: DataTypes.STRING
    },
    {
        tableName: 'categories',
        timestamps: false
    })

let Post = sequelize.define('posts', {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        title: DataTypes.STRING,
        image_url: DataTypes.STRING
    },
    {
        tableName: 'posts',
        timestamps: false
    })
    
let UserLike = sequelize.define('UserLike', {
        post_id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        },
        user_id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true
        }
    },
    {
        tableName: 'user_likes',
        timestamps: false
    })
    
Category.belongsToMany(Post, {
    through: 'category_posts',
    timestamps: false,
    foreignKey: 'category_id',
    otherKey: 'post_id'
})
Post.belongsToMany(Category, {
    through: 'category_posts',
    timestamps: false,
    foreignKey: 'post_id',
    otherKey: 'category_id'
})

Post.hasMany(UserLike, {
    foreignKey: 'post_id',
    sourceKey: 'id'
})
UserLike.belongsTo(Post, {
    foreignKey: 'post_id',
    targetKey: 'id'
})

And then I'd use a subquery like this. Note that the parentheses around the literal select statement are necessary in order to make it a sequelize subquery.

let results = await Category.findAll({
        attributes: {
            exclude: [ 'detail' ]
        },
        where: { id: request.params.id },
        include: {
            model: Post,
            attributes: {
                include: [
                    [ sequelize.cast(sequelize.literal('(select count(*) from user_likes as ul where ul.post_id = posts.id)'), 'integer'), 'likes' ]
                ]
            }
        }
    })

This produces the following results:

[
  {
    "id": 1,
    "category_name": "category 1",
    "posts": [
      {
        "id": 1,
        "title": "Post 1",
        "image_url": "http://example.com/image1.png",
        "likes": 3,
        "category_posts": {
          "category_id": 1,
          "post_id": 1
        }
      },
      {
        "id": 2,
        "title": "Post 2",
        "image_url": "http://example.com/image2.png",
        "likes": 2,
        "category_posts": {
          "category_id": 1,
          "post_id": 2
        }
      }
    ]
  }
]

Alternatively, as suggested by HarshIT, you could use aggregation and a group by clause with the following query:

let results = await Category.findAll({
        attributes: {
            exclude: [ 'detail' ],
            include: [[sequelize.cast(sequelize.fn('COUNT', Sequelize.col('posts->UserLikes.post_id')), 'integer'), 'total_likes']]
        },
        where: { id: request.params.id },
        include: {
            model: Post,
            include: {
                model: UserLike
            }
        },
        group: [
            'categories.id',
            'posts.id',
            'posts->category_posts.category_id',
            'posts->category_posts.post_id',
            'posts->UserLikes.post_id',
            'posts->UserLikes.user_id'
        ]
    })

Which would produce results of the form:

[
  {
    "id": 1,
    "category_name": "category 1",
    "total_likes": 1,
    "posts": [
      {
        "id": 1,
        "title": "Post 1",
        "image_url": "http://example.com/image1.png",
        "category_posts": {
          "category_id": 1,
          "post_id": 1
        },
        "UserLikes": [
          {
            "post_id": 1,
            "user_id": 5
          },
          {
            "post_id": 1,
            "user_id": 6
          },
          {
            "post_id": 1,
            "user_id": 9
          }
        ]
      },
      {
        "id": 2,
        "title": "Post 2",
        "image_url": "http://example.com/image2.png",
        "category_posts": {
          "category_id": 1,
          "post_id": 2
        },
        "UserLikes": [
          {
            "post_id": 2,
            "user_id": 5
          },
          {
            "post_id": 2,
            "user_id": 8
          }
        ]
      }
    ]
  }
]