This is the first day of my participation in the August Challenge. For details, see:August is more challenging

1. Model association

Sequelize provides four types of association:

HasOneAssociation types
BelongsToAssociation types
HasManyAssociation types
BelongsToManyAssociation types

1. One-to-one association (HasOne, BelongsTo)

One-to-one is a relatively simple association relationship, which is generally used for the association relationship between the extended table and the main table of a data table. For example, the system has a user table user and the basic information of users. The wechat login function is added in the later product iterations, and a wechat user extension table wechat_user is usually added to store the wechat information of users. We only query wechat information when we need it. In this scenario, we can create a one-to-one association between the two models.

Table structure and model creation

usermodel

// app/model/user.js
module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true,},nickname: STRING(20),
    password: STRING(32),
    create_time: DATE,
    update_time: DATE,
    delete_time: DATE,
  }, {
    freezeTableName: false.tableName: 'user'.underscored: false.paranoid: true.timestamps: true.createdAt: 'create_time'.updatedAt: false.deletedAt: 'delete_time'});return User;
};
Copy the code

Wechat_user model

// app/model/wechat_user.js
module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const WechatUser = app.model.define('wechatUser', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true,},user_id: INTEGER,
    nickname: STRING,
    avatar_url: STRING,
    open_id: STRING,
    gender: INTEGER,
    create_time: DATE,
  }, {
    freezeTableName: false.tableName: 'wechat_user'.underscored: false.timestamps: true.createdAt: 'create_time'.updatedAt: false.deletedAt: false});return WechatUser;
};
Copy the code

Description of model parameters:

FreezeTableName: forces the tableName to be equal to the model name, set to false in this example, and tells Sequelize the tableName directly with tableName.

Description: Whether to use the hump to underline column field names.

Timestamps: Whether or not this field is automatically written. Used with createdAt, updatedAt, and when creating or updating content.

Creating an Association (HasOne)

Modify the User model and add the following content to the model to specify the association
```
// app/model/user.js
module.exports = app= >{...constUser = ... . User.associate =() = > {
    app.model.User.hasOne(app.model.WechatUser, {
      as: 'wechat_user'.foreignKey: 'user_id'.sourceKey: 'id'}); };return User;
}
Copy the code
```
As: alias, which is equivalent to naming the association. This is the name of the associated result set field in the query result.

ForeignKey: user-defined foreignKey. The current association is equivalent to user as the primary table, wechat_user as the associated table, and foreignKey is the field (user_id) associated with user in wechat_user.

SourceKey: primary key of the primary table user.
Create a reverse one-to-one association (BelongsTo)

Example Modify the WechatUser model to specify the reverse association
```
// app/model/wechat_user.js
module.exports = app= >{... WechatUser.associate =() = > {
    app.model.WechatUser.belongsTo(app.model.User, {
      as: 'user'.foreignKey: 'user_id'.targetKey: 'id'}); };return WechatUser;
}
Copy the code
```
In reverse one-to-one mode, the user table is used as the primary table, but the wechat_user table is used as the reverse query.

TargetKey: targetKey, the primary key of the reversely associated primary table.

Examples of association relationships

One-to-one correlation
```
class UserService extends Service {
  async user (id) {
    const { User, WechatUser } = this.app.model;
    return await User.findOne({
      where: { id },
      attributes: { exclude: [ 'password'.'delete_time'],},include: [{as: 'wechat_user'.model: WechatUser, } ] }); }}Copy the code
```
Attributes: query the field information. You can exclude the field to query, or you can directly pass in the array attributes: [‘id’, ‘nickname’]).

Include: specifies the association of the query. As must be the same as the ONE that defines the association. Model Specifies the model of the association.

Print the user as follows:

Inverse one-to-one correlation

class UserService extends Service {
  async wechatUser (id) {
    const { WechatUser, User } = this.app.model;
    return await WechatUser.findOne({
      where: { id },
      include: [{as: 'user'.model: User,
          attributes: { exclude: [ 'password'.'delete_time'],},},],}); }}Copy the code

You can also pass various query parameters in include, such as query attributes, query condition where, and so on.

The query results are as follows:

2. One-to-many association (HasMany)

One-to-many is also a common association, which is used to define an association between a single model and multiple other models. For example, in the user table above, each user can publish multiple articles, but the articles can only belong to one user, and the user and article belong to a one-to-many relationship.

Added article table and model

// app/service/article.js
const Article = app.model.define('article', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true,},title: STRING(255),
    content: STRING,
    user_id: INTEGER,
    create_time: DATE,
    update_time: DATE,
    delete_time: DATE,
  }, {
    freezeTableName: false.tableName: 'article'.underscored: false.paranoid: true.timestamps: true.createdAt: 'create_time'.updatedAt: false.deletedAt: 'delete_time'});Copy the code

Create a one-to-many association

Modify User model:

// app/model/user.js
module.exports = app= >{...constUser = ... . User.associate =() = >{... app.model.User.hasMany(app.model.Article, {as: 'articles'.foreignKey: 'user_id'.sourceKey: 'id'}); };return User;
}
Copy the code

Using Associated Query

const { User, Article } = this.app.model;
const user = await User.findOne({
  where: { id: 1 },
  attributes: { exclude: [ 'password'.'delete_time'],},include: [{as: 'articles'.model: Article, attributes: { exclude: [ 'delete_time'],}}]});Copy the code

Print the results:

3. Many-to-many association (BelongsToMany)

Many-to-many associations connect a source to multiple targets, all of which can be connected to other sources besides the first target.

This cannot be expressed by adding a foreign key to one of the tables, as with other relationships. Instead, use the concept of a join model. This will be an additional model (and an additional table in the database) that will have two foreign key columns and trace associations.

Many-to-many relationships are more complex than one-to-one or one-to-many relationships. For example, a user can play multiple roles, and a role can belong to multiple users. Use intermediate tables to establish associations. Create a new model role with the existing model User. The associated table roLE_user contains foreign keys user_id and role_id.

Model to create

Role model

module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  return app.model.define('role', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true,},name: STRING,
    create_time: DATE,
  }, {
    freezeTableName: false.tableName: 'role'.timestamps: false}); };Copy the code

Role_user model

module.exports = app= > {
  const { INTEGER } = app.Sequelize;
  return app.model.define('role_user', {
    user_id: {
      type: INTEGER,
      references: { model: app.model.User, key: 'id',}},role_id: {
      type: INTEGER,
      references: { model: app.model.Role, key: 'id',},},}, {freezeTableName: false.tableName: 'role_user'.underscored: true.timestamps: false}); };Copy the code

References: The given model will be used as the join model, specifying the constraints for the fields.

Creating an Association

Add the following code to the User model:
```
// app/model/user.js
module.exports = app= >{...constUser = ... . User.associate =() = >{... app.model.User.belongsToMany(app.model.Role, {as: 'user_roles'.through: app.model.RoleUser,
      foreignKey: 'user_id'.otherKey: 'role_id'}); };return User;
}
Copy the code
```
If foreignKey and otherKey are not specified, the default foreign keys used by Sequelize are userId and roleId. To change these names,Sequelize accepts the parameters foreignKey and otherKey, respectively (that is,foreignKey defines the key of the source model in the join relationship, and otherKey defines the key in the target model).

You can use the same method to create associations in the Role model. You can query the list of users bound to the Role. ForeignKey and otherKey are the opposite here.

Using Associated Query

Add a method to the service to query a list of users, each of which contains all roles for the user:

class UserService extends Service {
  async users () {
    const { User, Role } = this.app.model;
    return await User.findAll({
      attributes: {
        exclude: [ 'password'.'delete_time'],},include: [{as: 'user_roles'.model: Role, }, ], }); }}Copy the code

Print the results:

2. Logical deletion

In projects, frequent data deletion operations can cause performance problems. The logical deletion function is to add a column of fields to the data to mark whether the data is deleted. It is not really deleted, but also convenient for data recovery when necessary.

In Sequelize this feature is called Paranoid, and the odd name means that you have to go through the document several times to find it.

Take the User model above as an example. To implement logical deletion, the exception must pass the paranoid: true argument to the model definition. You need to have automatic write times enabled for this to work (that is, if you pass timestamps: false, logical deletes won’t work).

You can change the logical delete default field name (the default is deletedAt) to something else. Set createdAt and updatedAt to false if you don’t want to automatically write the create time and update time.

Model definition logical deletion

// app/model/user.js
module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const User = app.model.define('user', {...// Omit n lines of field definitions}, {...paranoid: true.timestamps: true.createdAt: 'create_time'.updatedAt: false.deletedAt: 'delete_time'});return User;
};
Copy the code

Use of logical delete

UPDATE ‘User’ SET ‘delete_time’ =? WHERE `id` = ? Statement to set delete_time to the current time. The condition ““delete_time ‘IS NULL’ IS automatically added during the query.

class UserService extends Service {
  async deleteUser (id) {
    const { User } = this.app.model;
    const user = await User.findOne({ where: { id } });
    awaituser.destroy(); }}Copy the code

Three, obtain, virtual field

1. Access

The function of the harvester is to process the original value of the field in the model and output the processed data.

Take the WechatUser model described above as an example. After the query, the output time format is 2021-08-04T03:18:32.000Z, which does not meet the requirements. The format of the output time is YYYY-MM-DD HH: MM: SS.

Define a transformation time method, using dayJS, add a get method to the field, and return the formatted data.

// app/model/wechat_user.js
// Define a method to convert time
const dayjs = require('dayjs');
const formatDate = date= > (date ? dayjs(date).format('YYYY-MM-DD HH:mm:ss') : date);
module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const WechatUser = app.model.define('wechatUser', {...// Omit other fields to prevent interference
    create_time: {
      type: DATE,
      get () {
        // Return the modified time
        return formatDate(this.getDataValue('create_time')); },},}, {...// Omit the configuration
  });
  return WechatUser;
};
Copy the code

“Create_time “: “2021-08-04 13:18:32”

2. Virtual fields

Virtual fields are fields that Sequelize populates in the background, but they don’t actually exist in the database.

Again in the WechatUser model, we need to show the gender of the user gender (male, female, secret) but we need to keep the original field gender in the data, We can use Sequelize’s DataTypes.VIRTUAL to add a field that does not exist in the table, gender_TEXT. Continue to modify the model:

// app/model/wechat_user.js
// Define gender enumeration
const genderMap = { 0: 'secret'.1: 'male'.2: 'woman'};module.exports = app= > {
  const { STRING, INTEGER, DATE, VIRTUAL } = app.Sequelize;
  const WechatUser = app.model.define('wechatUser', {...// Omit other fields to prevent interference
    gender: INTEGER,
    gender_text: {
      type: VIRTUAL,
      get () {
        const gender = this.getDataValue('gender');
        return genderMap[gender] ? genderMap[gender] : ' '; },}}, {...// Omit the configuration
  });
  return WechatUser;
};
Copy the code

Gender_text: gender_text; gender_text: gender_text;

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Egg. Use js Sequelize logic model associations, deleting, getter | August more challenges

1. Model association

1. One-to-one association (HasOne, BelongsTo)

2. One-to-many association (HasMany)

3. Many-to-many association (BelongsToMany)

2. Logical deletion

Three, obtain, virtual field

1. Access

2. Virtual fields

Egg. Use js Sequelize logic model associations, deleting, getter | August more challenges

1. Model association

1. One-to-one association (HasOne, BelongsTo)

2. One-to-many association (HasMany)

3. Many-to-many association (BelongsToMany)

2. Logical deletion

Three, obtain, virtual field

1. Access

2. Virtual fields

Related Posts

MySQL Group Replication MySQL Group Replication

Hand in hand, take you to use the Beego Mall series a (basic)

ReplacingMergeTree: Implement Clickhouse data updates