There are three models User,AppGroup & GroupUser defined as follows
const sequelize = require('../utils/database')
const bcrypt = require("bcrypt");
var moment = require('moment')
const models = require('../models')
User Model
module.exports = function (sequelize, DataTypes) {
const User = sequelize.define('User', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
firstName: {
type: DataTypes.STRING,
field: 'firstName',
validate: {
notEmpty: {
args: true,
msg: "firstName is Required"
},
is: {
args: ["^[a-z]+$", 'i'],
msg: "In firstName Only letters allowed"
},
len: {
args: [4, 32],
msg: "firstName string length is not in this range"
}
}
},
lastName: {
type: DataTypes.STRING,
field: 'lastName',
validate: {
notEmpty: {
args: true,
msg: "lastName is Required"
},
is: {
args: ["^[a-z]+$", 'i'],
msg: "In lastName Only letters allowed"
},
len: {
args: [2, 32],
msg: "lastName string length is not in this range"
}
}
},
email: {
type: DataTypes.STRING,
validate: {
isEmail: {
msg: "Email is not valid"
},
isUnique: function (value, next) {
User.findOne({
where: {
email: value
}
}).then(function (result) {
if (result === null) {
return next()
} else {
return next(' Email already in use')
}
}).catch(err => {
return next()
})
}
}
},
password: DataTypes.STRING,
createdAt: {
type: DataTypes.DATE,
get: function () { // or use get(){ }
var originalDate = this.getDataValue('createdAt')
return moment(originalDate).utcOffset(330).format('YYYY-MM-DD HH:mm:ss');
}
},
updatedAt: {
type: DataTypes.DATE,
get: function () { // or use get(){ }
var originalDate = this.getDataValue('createdAt')
return moment(originalDate).utcOffset(330).format('YYYY-MM-DD HH:mm:ss');
}
}
}, {
validate: {
modelLevel() {
var pattern = /^(?=.*[0-9])(?=.*[!@#$%^&*])[a-zA-Z0-9!@#$%^&*]{6,16}$/;
if (!pattern.test(this.password)) {
throw new Error("Password should contain atleast one special character,upper case ,lowercase & number")
}
}
},
freezeTableName: true,
hooks: {
beforeCreate: async (user, options) => {
user.password = await bcrypt.hash(user.password, bcrypt.genSaltSync(8))
}
},
classMethods: {
//this won't work
emailAlreadyTaken: async function (email) {
const result = await this.findOne({ where: { email } });
return result !== null;
},
},
instanceMethods: {
//does not work
CapitalizeFirstName: function () {
var upperCaseFirstName = this.get("firstName").toUpperCase()
this.set("firstName", upperCaseFirstName)
return this.save()
}
}
});
User.associate = (models) => {
User.belongsToMany(models.AppGroup, {
through: 'GroupUser',
as: 'Group',
foreignKey: 'UserId'
});
};
//this.work on instance
User.prototype.getFullName = function () {
return this.firstName + " " + this.lastName
};
//this.work on instance
User.prototype.CapitalizeFirstName = function () {
var upperCaseFirstName = this.get("firstName").toUpperCase()
this.set("firstName", upperCaseFirstName)
return this.save()
}
return User;
}
AppGroup Model
var express = require('express');
var router = express.Router();
var db = require('../models')
router.get('/user', async function (req, res, next) {
var users = await db.User.findAll({
include: [{
model: db.AppGroup,
attributes: ['desc', 'name', 'admin', 'id'],
//where:{name:'Research'},
//through: { where: { groupId: 6 } },
as: 'Group'
}]
})
res.json({
"msg": "all employee",
"data": users
})
});
router.get('/group', async function (req, res, next) {
var groups = await db.AppGroup.findAll({
include: [{
model: db.User,
attributes: ['firstName', 'lastName', 'email', 'id'],
//through: { where: { groupId: 6 } },
as: 'User'
}]
})
res.json({
"msg": "group",
"data": groups
})
});
module.exports = router;
Group User Model
const sequelize = require('../utils/database')
const models = require('../models')
const QueryString = require('qs')
module.exports = function (sequelize, DataTypes) {
const GroupUser = sequelize.define('GroupUser', {
UserId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: 'User',
key: 'id'
}
},
groupId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: 'AppGroup',
key: 'id'
}
}
},{
freezeTableName: true
});
return GroupUser;
};
Here GroupUser table act as middle table which keep map of AppGroup & User through columns GroupId & UserId
In User Model we define Assocition as
User.associate = (models) => {
User.belongsToMany(models.AppGroup, {
through: 'GroupUser',
as: 'Group',
foreignKey: 'UserId'
});
};
In AppGroup Model we define association as
AppGroup.associate = (models) => {
AppGroup.belongsToMany(models.User, {
through: 'GroupUser',
as: 'User',
foreignKey: 'groupId'
});
};
Now How to query
var users = await db.User.findAll({
include: [{
model: db.AppGroup,
attributes: ['desc', 'name', 'admin', 'id'],
//where:{name:'Research'},
//through: { where: { groupId: 6 } },
as: 'Group'
}]
})
Here we get User as main result in which AppGroup & GroupUser nexted
Another Query
var groups = await db.AppGroup.findAll({
include: [{
model: db.User,
attributes: ['firstName', 'lastName', 'email', 'id'],
//through: { where: { groupId: 6 } },
as: 'User'
}]
})
Here we get Group as main result in which User & GroupUser nexted
In above query through part is commented here we can add filter condition on
GroupUser table columns.
code of this example is at
https://github.com/gitsangramdesai/sequelize-validation
No comments:
Post a Comment