This is the 11th day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021

Sequelize User-defined table names

Simply add the tableName field when you define the model.

module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true },
    username: STRING(30),
    age: INTEGER,
    sex: STRING(30),
    created_at: DATE,
    updated_at: DATE,
  },{
    tableName: "usera"
  });

  return User;
};
Copy the code
  • Set not to display timestamp fields such as createdAt
{
    tableName: "usera".timestamps: false
}
Copy the code

Use Sequelize for multi-table queries

Note: If you are using underscore nomenclature in your database, you will need to use camelback nomenclature in model.

One-to-one query

  • Article table structure

  • Structure of the article_cate table

  • The model of the article
'use strict';

module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const Article = app.model.define('article', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true },
    title: STRING(255),
    description: STRING(255),
    cateId: INTEGER,
    state: INTEGER
  },{
    tableName: "article".timestamps: false
  });
  // The core code to implement the association is the following statement
  Article.associate = function() {
    / / 1 to 1
    app.model.Article.belongsTo(app.model.ArticleCate,{foreignKey: 'cateId'});
  }

  return Article;
};
Copy the code
  • Article_cate model
'use strict';

module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const ArticleCate = app.model.define('article_cate', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true },
    title: STRING(255),
    state: INTEGER
  },{
    tableName: "article_cate".timestamps: false
  });

  return ArticleCate;
};
Copy the code
  • One-to-one query method
async onevsone() {
    const result = await this.ctx.model.Article.findAll({
      include: {
        model: this.ctx.model.ArticleCate
      }
    });
    this.ctx.body = result;
  }
Copy the code

The query result is as follows:

One-to-many query

  • article_cate.js
ArticleCate.associate = function () {
    / / 1 to 1
    app.model.ArticleCate.hasMany(app.model.Article, { foreignKey: 'cateId' });
    
  }
Copy the code
  • routing
  async onevsmany() {
    const result = await this.ctx.model.ArticleCate.findAll({
      include: {
        model: this.ctx.model.Article
      }
    });
    this.ctx.body = result;
  }
Copy the code

Many-to-many query

Many-to-many queries mainly use belongsToMany.

  • A course can be taken by more than one student
Lesson.associate = function(){
// A course can be taken by more than one student
    app.model.Lesson.belongsToMany(app.model.Student, {
    through: app.model.LessonStudent,
    foreignKey: 'lessonId'.otherKey: 'studentId'
    });
}
Copy the code
  • A student can take more than one course (the through in the code refers to the middle table)
Student.associate = function (){
    // A student can take more than one course
    app.model.Student.belongsToMany(app.model.Lesson, {
    through: app.model.LessonStudent,
    foreignKey: 'studentId'.//
    otherKey: 'lessonId'
    });
}
Copy the code
  • The query
const { ctx } = this;
let result = await ctx.model.Student.findAll({
    include: {
    model: ctx.model.Lesson
    }
});
Copy the code