CODEWITHSUNDEEP
node.jspostgresqlormsequelize.js
HarshIT
HarshIT

Reputation: 4915

How to count associations in sequelize in postgres database

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

Upvotes: 1

Views: 672

Answers (1)

Andrew
Andrew

Reputation: 6514

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

Upvotes: 2

Related Questions