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