Sequelize多表动态条件查询支持分页

一、编写Model

关联关系

1. 参考链接：Associations - 关联

2. 关联关系用法

   User表和Account表，一对一关系,Account表中有外键user_id：

   User.hasOne(Account,{foreignKey:'user_id'})
   Account.blongsTo(User,{ foreignKey: 'user_id' })
   
   

3. 源码

   ...
   User.associate = function() {
       app.model.User.hasOne(app.model.Account, { foreignKey: 'user_id' });
       app.model.User.hasMany(app.model.Log, { foreignKey: 'user_id', targetKey: 'log_id' }); 
   };
   ...
   

scope条件查询

1. 参考链接：Scopes - 作用域

2. 用法

   const users = await this.ctx.model.Account.scope([{ method: [ 'hasStatus', status ] }]).findAndCountAll();
   

3. 在model中添加scopes

   ...
   scopes: {
       hasName(e = false) {
           if (!e) return {};
           return {
             where: { name: { [Op.like]: `%${e}%` } },
           };
       },
       hasEnterprise(e = false) {
           if (!e) return {};
           return {
             where: { enterprise: { [Op.like]: `%${e}%` } },
           };
       }
   },
   ...
   

4. model源代码

   //model文件
   const { Op } = require('sequelize');
   module.exports = app => {
     const User = app.model.define('user',{},{
       sequelize: app.Sequelize,
       modelName: 'user',
       tableName: 'user',
       //scope，支持条件查询
       scopes: {
           hasName(e = false) {
               if (!e) return {};
               return {
                 where: { name: { [Op.like]: `%${e}%` } },
               };
           },
           hasEnterprise(e = false) {
               if (!e) return {};
               return {
                 where: { enterprise: { [Op.like]: `%${e}%` } },
               };
           }
       },
     });
     //多表查询关联关系
     User.associate = function() {
       app.model.User.hasOne(app.model.Account, { foreignKey: 'user_id' });
       app.model.User.hasMany(app.model.Log, { foreignKey: 'user_id', targetKey: 'log_id' }); 
     };
     return User;  
   }
   

二、编写Controller

多表查询

1. 参考链接：Querying - 查询
2. 用法

   this.ctx.model.Account.scope().findAndCountAll({
       include: [{model: this.ctx.model.User }],
     });
   

3. 源码

   //Controller文件
   async index() {
       const res = this.ctx.query;
       const page = Number(res.page) || 1,
         page_size = Number(res.page_size) || 10,
         name = res.name,
         status = res.status,
         enterprise = res.enterprise;
       const users = await this.ctx.model.Account
           .scope([{ method: [ 'hasStatus', status ] }])
           .findAndCountAll({
               include: [{
                   model: this.ctx.model.User.scope([
                     { method: [ 'hasName', name ] },
                     { method: [ 'hasEnterprise', enterprise ] },
                   ]),
                   required: true,
               }],
               order: [ [ 'created_at', 'DESC' ]],//倒序
               //分页
               limit: page_size,
               offset: page_size * (page - 1),
           });
   }
   

三、原生mysql实现多表动态条件查询

3.1 编写sequelize.js文件

/database目录下新建sequelize.js文件:

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize(process.env.MYSQL_DATABASE, process.env.MYSQL_USER, process.env.MYSQL_PASSWORD, {
  dialect: 'mysql',
  host: process.env.MYSQL_HOST,
  port: process.env.MYSQL_PORT,

});
module.exports = sequelize;

3.2 配置.env

# mysql配置
MYSQL_HOST = 127.0.0.1
MYSQL_PORT = 3306
MYSQL_DATABASE = ''
MYSQL_USER = root
MYSQL_PASSWORD = ''

3.3 编写controller文件

const sequelize = require('../../../database/sequelize');
class AuthController extends Controller  {
    async list() { // 原生mysql获取列表
        const res = this.ctx.query;
        const page = Number(res.page) || 1,
          page_size = Number(res.page_size) || 10,
          name = res.name,
          status = res.status,
          enterprise = res.enterprise;
        const sql = 'SELECT * FROM `account` JOIN `user` ON account.user_id=user.id OR (user.name LIKE IF(?="",1=1,"%?%") AND account.status=IF(? ="",1=1,account.status=?) AND user.enterprise LIKE IF(?="",1=1,"%?%")) ORDER BY "created_at" DESC LIMIT ?,? ';
        const users = await sequelize.query(sql, {
          replacements: [ status, status, name, name, enterprise, enterprise, page_size * (page - 1), page_size ],
          type: sequelize.QueryTypes.SELECT,
          plain: false,
        });
    }
    ...
}