sequelize - how to count the total record in other table

Viewed 112

I am using Sequelize and mysql to manage databases.

I have 2 tables user and organization at first, which are N-to-M relationships, so I created a junction table user_organization for these 2 tables.

I want to add a field totalNumberOfUserOrganization in table organization, which counts the total number of records in user_organization, whose organizationId is equal to id in table organization.

This is the data in current database:

{
    "users": [
        {
            "id": "user01"
        },
        {
            "id": "user02"
        },
        {
            "id": "user03"
        }
    ],
    "organization": [
        {
            "id": "org01"
        },
        {
            "id": "org02"
        }
    ],
    "user_organization": [
        {
            "id": 1,
            "userId": "user01",
            "organizationId": "org01"
        },
        {
            "id": 2,
            "userId": "user01",
            "organizationId": "org02"
        },
        {
            "id": 3,
            "userId": "user02",
            "organizationId": "org02"
        },
        {
            "id": 4,
            "userId": "user03",
            "organizationId": "org02"
        },
    ]
}

I want to achieve the result like below:

{
    "users": [...],
    "organization": [
        {
            "id": "org01",
            "totalNumberOfUserOrganization": 1
        },
        {
            "id": "org02",
            "totalNumberOfUserOrganization": 3
        }
    ],
    "user_organization": [...]
}

I also want to sort the organization record by totalNumberOfUserOrganization.

Is there any way to achieve it?

user.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const user = sequelizeClient.define('user', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  user.associate = function (models) {
    this.belongsToMany(models.organization, { as: 'organizations', through: 'user_organization', onDelete: 'cascade' })
  };

  return user;
};

organization.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const organization = sequelizeClient.define('organization', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.STRING,
    },
    totalNumberOfUserOrganization: {
       // How to get the total number of record in user_organization with same id here
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  organization.associate = function (models) {
    this.belongsToMany(models.users, { as: 'users', through: 'user_organization', onDelete: 'cascade' })
  };

  return organization;
};

user-organization.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const userOrganization = sequelizeClient.define('user_organization', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false,
      primaryKey: true
    },
    userId: {
        type: DataTypes.UUID,
        allowNull: false,
    },
    organizationId: {
        type: DataTypes.STRING,
        allowNull: false,
    },
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    },
    indexes: [
        { name: 'ix_user_organization', unique: true, fields: ['userId', 'organizationId'] },
    ],
  });

  userOrganization.associate = function (models) {
  };

  return userOrganization;
};

0 Answers
Related