node.js-sequelize动态多条件查询

1.安装依赖

npm install --save mysql2 
npm install --save sequelize

2.连接数据库

// 下面的global 是我自己demo中的全局变量（启动文件中已注册好的）

// 引入依赖
const Sequelize = require('sequelize')

// 模糊查询 like用的
const Op = Sequelize.Op;

// 连接数据库
const sequelize = new Sequelize(global.DB.database, global.DB.user, global.DB.password, {
    host: global.DB.host,
    dialect: 'mysql', // 这里可以改成任意一种关系型数据库

    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000,
    },
})

// 测试连接是否成功
sequelize
.authenticate()
.then(() => {
	console.log('Connection has been established successfully.')
})
.catch(err => {
	console.log('Unable to connect to the database', err)
})

3.根据model自动建表

// 根据 model自动创建表
sequelize
    .sync()
    .then(() => {
        console.log('init db ok')
    })
    .catch(err => {
        console.log('init db error', err)
    })

/**
 * id          int      主键       自增
 * name        string   书名       可空
 * author      string   作者       可空
 * desc        string   简介       可空
 * content     string   内容       可空
 * cover       string   封面图     可空
 * channel_id  string   栏目       非空
 * 
 * 
 * timestamps设置为 false 之后，将不会自动加上 createdAt, updatedAt 这两个字段
 * tableName 自定义表名
 * @type {ModelCtor<Model>}
 *
 */
const BookModel = sequelize.define('book', {
    id: {
        type: Sequelize.INTEGER(11),
        primaryKey: true,            // 主键
        autoIncrement: true,         // 自动递增
    },
    name: Sequelize.STRING(255),
    author: Sequelize.STRING(255),
    desc: Sequelize.STRING(255),
    content: Sequelize.STRING(255),
    cover: Sequelize.STRING(255),
    channel_id: {
        type: Sequelize.INTEGER(11),
        allowNull:false,//非空验证
    }
}, {
    timestamps: false,
    tableName: 'book'
})

module.exports = { BookModel , Op}

4.注册全局变量

// 在启动文件app.js中，注册全局
global.BookModel = require('./linkDataBase/index').BookModel
global.Op = require('./linkDataBase/index').Op

5.getBook.js

// 打印日志用的 相当于控制台打印
let tafLogs = require('@taf/taf-logs');
let getBookLog = new tafLogs('TafDate','getBookLog');


async function getBook(req) {
    const BookModel = global.BookModel
    const Op = global.Op

    let channel_id = req.type;
    let id = req.id;
    let name = req.keywords;

    const attributes = []
    const where = {}
    const order = [
        ['id', 'DESC'] , // 逆序
    ]

    if(channel_id !== '') {
        attributes.push('channel_id')
        where['channel_id'] = channel_id
    }
    if(id !=='') {
        attributes.push('id')
        where['id'] = id
    }
    if(name !=='') {
        attributes.push('name')
        where['name'] = {
            [Op.like]: '%' +name + '%'
        }
    }
    try {
        if(attributes.length){
            getBookLog.info("查询图书-入参："+JSON.stringify(req));
            const BookData = await BookModel.findAll({
                attributes,
                where,
                order
            })
            getBookLog.info("查询图书-返回："+JSON.stringify(BookData));
            const data = []
            if(BookData.length){
                BookData.forEach(item=>{
                    data.push(item.dataValues)
                })
                return {
                    code: 200,
                    message: '成功',
                    data: data
                }
            } else{
                return {
                    code: 403,
                    message: '失败',
                    data: data
                }
            }
        }
        else{
            getBookLog.info("查询图书-入参："+JSON.stringify(req));
            const BookData = await BookModel.findAll({
                order
            })
            getBookLog.info("查询图书-返回："+JSON.stringify(BookData));
            const data = []
            if(BookData.length){
                BookData.forEach(item=>{
                    data.push(item.dataValues)
                })
                return {
                    code: 200,
                    message: '成功',
                    data: data
                }
            } else{
                return {
                    code: 403,
                    message: '失败',
                    data: data
                }
            }
        }

    }catch (e) {
        console.log(e);
    }
}


module.exports = getBook

6.原文地址

原文地址