Find Count From Other Table in Sequelize

Question

I have a problem in sequelize with node js. I want product count according to category.

My category model is define as:

const Sequelize = require('sequelize');
const sequelize = require('../configs/db-connection.config');
const Product = require('../models/product.model');
const Category = sequelize.define(
  'category',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false
    },
  },
  { timestamps: true }
);
Category.hasMany(Product);
Product.belongsTo(Category);
module.exports = Category;

My product model is define as:

const Sequelize = require('sequelize');
const sequelize = require('../configs/db-connection.config');
const Product = sequelize.define(
  'product',
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false
    },
    categoryRef: {
      type: Sequelize.INTEGER,
      allowNull: false,
      foreignKey: true,
      references: {
        model: CATEGORY.TABLE_NAME,
        key: 'id'
      }
    }   
  },
  { timestamps: true }
);

module.exports = Product;

Here each category is connected to product as a foreignKey in product model like categoryRef. Let me give you an example one category is Devices and its product will be Laptop, Monitor, CPU etc. If devices have 3 products then it will return 3 as a count in category. Here each object in array represents to category obj and I want to add an extra field i.e. count in category obj and it will give me the count of products which is stored as a foreignKey in product table.

My expected result is:

[
  {
    id: 1,
    name: 'Devices',
    createdAt: '2020-01-17T12:08:10.000Z',
    updatedAt: '2020-01-17T12:11:22.000Z',
    count:3
  },
  {
    id: 2,
    name: 'appliances',
    createdAt: '2020-01-23T07:59:27.000Z',
    updatedAt: '2020-01-23T08:12:54.000Z',
    count:0
  },
  {
    id: 3,
    name: 'furniture',
    createdAt: '2020-01-23T08:51:35.000Z',
    updatedAt: '2020-01-23T08:51:35.000Z',
    count:0
  },
];

I already applied following sql query on database which gives perfect result:

SELECT inventory.categories.*, count(products.categoryRef) as count
from inventory.categories
left join inventory.products
on (inventory.categories.id = inventory.products.categoryRef)
group by
inventory.categories.id

But I don't know how to convert it into sequelize methods. Please help me to find out the solution which methods I need to use to get the desired output. Thanks in Advance.

Prakash Karena Prakash Karena · Accepted Answer · 2020-01-30T06:13:04

You can use attributes and include to get your category with counts of products associated with it

Category.findAll({
        attributes: {
          include: [
            [
              Sequelize.fn('COUNT', Sequelize.col('products.categoryRef')),
              'productsCount'
            ]
          ]
        },
        include: [
          {
            model: Product,
            attributes: []
          }
        ],
        group: ['id']
      });

Find Count From Other Table in Sequelize

1 Answers