[db] sequelize association
hasOne
Creates an association between this (the source) and the provided target.
The foreign key is added on the target.
Example: User.hasOne(Profile). //This will add userId to the profile table.
belongsTo
Creates an association between this (the source) and the provided target.
The foreign key is added on the source.
Example: Profile.belongsTo(User).// This will add userId to the profile table.
hasMany
Creates a 1:m association between this (the source) and the provided target.
The foreign key is added on the target.
Example: User.hasMany(Profile). //This will add userId to the profile table.
belongsToMany
Create an N:M association with a join table.
User.belongsToMany(Project, { through: 'UserProjects' })
Project.belongsToMany(User, { through: 'UserProjects' })
Defining through is required.
Sequelize would previously attempt to auto generate names but that would not always lead to the most logical setups.
as
When creating associations, you can provide an alias, via the as option.
This is useful if the same model is associated twice,
or you want your association to be called something other than the name of the target model.
foreign key
To get full control over the foreign key column added by sequelize,
you can use the foreignKey option.
It can either be a string, that specifies the name,
or and object type definition, equivalent to those passed to sequelize.define.
CASCADE
Creating an association will add a foreign key constraint to the attributes.
All associations use CASCADE on update and SET NULL on delete,
except for n:m, which also uses CASCADE on delete.
Set to true to run before-/afterDestroy hooks when an associated model is deleted because of a cascade.
For example if
User.hasOne(Profile, {onDelete: 'cascade', hooks:true}),
the before-/afterDestroy hooks for profile will be called when a user is deleted.
Otherwise the profile will be deleted without invoking any hooks
不管是hasOne还是belongsTo最前面的为触发主体。
more example
User.hasMany(Picture)
User.belongsTo(Picture, { as: 'ProfilePicture', constraints: false })
user.getPictures() // gets you all pictures
user.getProfilePicture() // gets you only the profile picture
User.findAll({
where: ...,
include: [
{ model: Picture }, // load all pictures
{ model: Picture, as: 'ProfilePicture' }, // load the profile picture. Notice that the spelling must be the exact same as the one in the association
]
})
user.addPicture(p) // Add a single picture
user.setPictures([p1, p2]) // Associate user with ONLY these two picture, all other associations will be deleted
user.addPictures([p1, p2]) // Associate user with these two pictures, but don't touch any current associations
user.addPicture(req.query.pid) // Here pid is just an integer, representing the primary key of the picture
var UserProjects = sequelize.define('UserProjects', {
started: Sequelize.BOOLEAN
})
User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })
jan.addProject(homework, { started: false }) // The homework project is not started yet
jan.setProjects([makedinner, doshopping], { started: true}) // Both shopping and dinner has been started
p1.UserProjects = {
started: true
}
user.setProjects([p1, p2], {started: false}) // The default value is false, but p1 overrides that.
one to one
取的数据是单向取的(在前边的)
取得是define(first parameter of define) 或是 as 的名
使用 belongsTo 更好理解
model
let A = await sequelize.define('tableA', {
name: {
type: Sequelize.STRING,
defaultValue: 'default A'
}
},
{
//自定义实际表名,一般默认的是 tableAs
tableName: 'A',
//使外键名为snake_case而不是camelCase，
// 这里貌似是生成后处理的，会把tableA也转了
//underscored: true
}
)
let B = await sequelize.define('tableB', {
name: {
type: Sequelize.STRING,
defaultValue: 'default B'
}
},
{
tableName: 'B'
}
)
belongsTo
A表中将添加默认 tableBId 外键名
A getTableB() 可以取到关系数据，但是B不行
A.belongsTo(B);
as
A表中将添加 targetModelNameId 外键名
A getTargetModelName() 可以取到关系数据，但是B不行
A.belongsTo(B,{as: 'targetModelName'});
foreignKey
A表中将添加 custom_fk 外键名
A getTableB() 可以取到关系数据，但是B不行
A.belongsTo(B, {foreignKey: 'custom_fk'});
targetKey
???这里还不是很理解
The target key is the column on the target model that the foreign key column on the source model points to.
By default the target key for a belongsTo relation will be the target model's primary key.
To define a custom column, use the targetKey option.
//A表中将添加 tableBId 外键名
//A.belongsTo(B, {targetKey: 'name'});
hasOne
hasOne will add an attribute tableAId to the B model!
Furthermore, B.prototype will gain the methods getUser and setUser according
to the first parameter passed to define.
B表中将添加默认 tableAId 外键名
A getTableB() 可以取到关系数据，但是B不行
其他与belongsTo 相近
A.hasOne(B);
one to many
data
await A.sync({force: true});
await A.create({name: 'AAA'});
await B.sync({force: true});
await B.create({name: 'BBB', tableAId: 1});
await B.create({name: 'BBB1', tableAId: 1});
await B.create({name: 'BBB2', tableAId: 1});
hasMany
B表中将添加默认 tableAId 外键名
A getTableBs() 可以取到关系数据(数组)，但是B不行
await A.hasMany(B);
many to many
model
let A = await sequelize.define('tableA', {
name: {
type: Sequelize.STRING,
defaultValue: 'default A'
}
},
{
//自定义实际表名,一般默认的是 tableAs
tableName: 'A',
//使外键名为snake_case而不是camelCase，
// 这里貌似是生成后处理的，会把tableA也转了
//underscored: true
}
)
let B = await sequelize.define('tableB', {
name: {
type: Sequelize.STRING,
defaultValue: 'default B'
},
type: {
type: Sequelize.STRING
}
},
{
tableName: 'B'
}
)
let C = await sequelize.define('tableC', {}, {
tableName: 'C'
})
data
await A.sync({force: true});
await A.create({name: 'AAA'});
await A.create({name: 'AAA1'});
await A.create({name: 'AAA2'});
await B.sync({force: true});
await B.create({name: 'BBB', type: '2'});
await B.create({name: 'BBB1'});
await B.create({name: 'BBB2'});
await C.sync({force: true});
await C.create({AId: 1, BId: 1});
through是必须的
foreignKey will allow you to set source model key in the through relation.
otherKey will allow you to set target model key in the through relation.
A getB() 可以取到关系数据(数组) ，但是C不行
B getA() 可以取到关系数据(数组) ，但是C不行
await A.belongsToMany(B, {through: C, as: 'B', foreignKey: 'AId', otherKey: 'BId'});
await B.belongsToMany(A, {through: C, as: 'A', foreignKey: 'BId', otherKey: 'AId'});
let ai = await A.findOne({where: {id: 1}});
let bi = await B.findOne({where: {id: 1}});
let prop = 2; //单数字或数组
await ai.addB(prop);
let ae = await ai.getB({
attributes: ['name'],
raw: true,
joinTableAttributes: []//清除关联数据表数据
});
let tt = await A.findAll({
include: [{
model: B,
through: {
attributes: ['type'],
where: {type: '2'}
}
}],//include 按说应该是有效的，然而并没有卵用
raw: true
});
