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