The difference between model and association in include when Sequelize connects and queries

Preface

Everyone knows that when using Sequelize to perform connection queries between relational models (tables), we will use model/as to specify the connection query model that already has an existing relationship, or through association to directly specify the connection query model relationship. So, in what scenarios should each be used?

1. Example preparation

Model definition

First, define the two models User and Company:

'use strict'

const Sequelize = require('sequelize');

// 创建 sequelize 实例
const sequelize = new Sequelize('db1', 'root', '111111', {logging: console.log});

// 定义User模型
var User = sequelize.define('user', {
 id:{type: Sequelize.BIGINT(11), autoIncrement:true, primaryKey : true, unique : true},
 name: { type: Sequelize.STRING, comment:'姓名' },
 sex: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0, comment:'性别' },
 companyId: { type: Sequelize.BIGINT(11), field: 'company_id', allowNull: false, comment:'所属公司' },
 isManager: { type: Sequelize.BOOLEAN, field: 'is_manager', allowNull: false, defaultValue: false, comment:'是否管理员'}
}, 
{ charset: 'utf8',
 collate: 'utf8_general_ci'});

// 定义Company模型
var Company = sequelize.define('company', {
 id:{ type:Sequelize.BIGINT(11), autoIncrement:true, primaryKey : true, unique : true},
 name: { type: Sequelize.STRING, comment:'公司名称' }
}, 
{ charset: 'utf8',
 collate: 'utf8_general_ci'});

// 定义User-Company关联关系
User.belongsTo(Company, {foreignKey:'companyId'});

// sequelize.sync({force:true}).then(() => {
// process.exit();
// });

As shown above, we defined two models, User and Company, and specified a 1:1 relationship between User and Company through belongsTo.

Insert data

Next, insert some test data based on the just-defined relationship model:

Company.create({name:'某公司'}).then((result) => {
 return Promise.all([
 User.create({name:'何民三', sex:1, companyId:result.id, isManager: true}),
 User.create({name:'张老二', sex:1, companyId:result.id})
 ])
}).then((result) => {
 console.log('done');
}).catch((err) => {
 console.error(err);
});

2. Use model /as

When performing connection query, if the association between models has been defined. You can specify the model to be connected to the query through the 'model' attribute in the include query option, and you can also specify an alias through the 'as' attribute.

For example, query a user from the User model and query the company information of the user:

var include = [{
 model: Company,
 as: 'company'
}];
User.findOne({include:include}).then((result) => {
 console.log(result.name + ' 是 '+result.company.name+' 的员工');
}).catch((err) => {
 console.error(err);
});

The query results are as follows:

何民三 是 某公司 的员工

3. When using association

to connect the query, if there is no connection relationship defined in advance between the two models to be connected to the query, or To use a connection relationship outside the definition. At this time, model relationships can be defined or redefined through association.

For example, query any company in the Company model and query the administrator of the company:

var include = [{
 association: Company.hasOne(User, {foreignKey:'companyId', as:'manager'}),
 where: {isManager:true}
}]

Company.findOne({include:include}).then((result) => {
 console.log(result.name +' 的管理员是 ' +result.manager.name);
}).catch((err) => {
 console.error(err);
});

Due to the relationship between Company-User The model relationship is not defined in advance, so you need to specify the association relationship to be used when connecting the query in the include option.

The query results are as follows:

某公司 的管理员是 何民三

In addition to being used to specify model relationships that have not been defined before, association can also be used to re-define models. relation. For example, suppose we define a 1:N relationship between Company-User through hasMany in advance. This relationship applies to all employees under the query company. In the above example, we need to check the company's administrator through a 1:1 relationship. Therefore, we can redefine the model relationship through association.

For more related articles on the difference between model and association in include during Sequelize connection query, please pay attention to the PHP Chinese website!