【koajs项目实践】四、sequelize的高级使用

这篇文章重点介绍sequelize在使用过程中面对较为复杂的sql需求等情况，这是多年通过不断尝试和阅读相关英文博客和文档研究出来的，希望可以帮大家解决在使用上也遇到的问题

自定义函数

假设使用场景：
某表存在父节点和子节点的关联关系，在数据库中已经创建对应通过当前子节点获取其父节点的函数，现在需要在对应的model编写该函数

async getParentById (id) {
        return await this.orm[this.opts.model].findAll({
            attributes: this.opts.attrs,
            where: this.orm.sequelize.fn('FIND_IN_SET', this.orm.sequelize.col('id'), this.orm.sequelize.literal('getParentList(' + id + ')'))
        });
    }

其中’getParentList‘为数据库中已经创建好的获取父节点的函数

CREATE DEFINER=`db_user`@`%` FUNCTION `getParentList`(rootId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN 
DECLARE sParentList varchar(1000); 
DECLARE sParentTemp varchar(1000); 
SET sParentTemp =cast(rootId as CHAR); 
WHILE sParentTemp is not null DO 
IF (sParentList is not null) THEN 
SET sParentList = concat(sParentTemp,',',sParentList); 
ELSE 
SET sParentList = concat(sParentTemp); 
END IF; 
SELECT group_concat(pid) INTO sParentTemp FROM tb_node where FIND_IN_SET(id,sParentTemp)>0; 
END WHILE; 
RETURN sParentList; 
END

其中’tb_node’就是一张节点表

多表关联

假设使用场景：
目前存在用户表user、用户评论表comment，现在要查询用户id=123的用户评论记录

comment表存在user_id外键，在对应的schema内，构建表关系

Comment.associate = function (models) {
        Comment.belongsTo(models.User, {'foreignKey': 'user_id', 'as': 'user'})
    }
    
return Comment;

在对应的model加入以下函数

async pageCountUserComments (userId, offset, limit) {

        return await this.orm[this.model].pageCount({
            include: [{model: this.orm.User, as: 'user'}],
            where: {'user_id': userId},
            order: [['id', 'desc']],
            offset: offset,
            limit: limit
        })
    }

数据集合内会同时包括comment对象和user对象，如果只需要comment对象，或者要做一些数据上的整合，可以通过扩展schema的方法来处理

在对应的schema内扩展出一个方法来

	//添加新的属性
	Comment.prototype.sanitize = function () {
	    //获取当前数据
        let data = this.get()

        let _user = data.user;
        let _comment = data.comment;
		//精简字段
		let user = {'id': _user.id, 'name': _user.name, 'age': _user.age}
		let comment = {'content': commnet.content, 'type': commnet.type}
        
        return {'user': user, 'comment': comment}
    }
	//扩展一个新的方法，提供给该model调用
    Comment.pageCountUserJoin = function (options) {
        return this.findAndCountAll(options).then(function (recordObj) {
        	//遍历结果集
            let new_rows = recordObj.rows.map( function (record) {
            	//实际调用上面的sanitize方法
                return record.sanitize();
            })
			//拼接新的结果集
            let result = {'rows': new_rows, 'count': recordObj.count}

            return result
        })
    }

在comment的model中调用

async pageCountUserComments (userId, offset, limit) {
		//改用新扩展的方法
        return await this.orm[this.model].pageCountUserJoin({
            include: [{model: this.orm.User, as: 'user'}],
            where: {'user_id': userId},
            order: [['id', 'desc']],
            offset: offset,
            limit: limit
        })
    }

自定义sql

koa-orm模块中，还可以直接执行sql语句，适合复杂的sql语法场景

//查询集合记录
let querySql = 'select u.id, u.name, c.comment, c.type from comment as c left join user as u on c.user_id = u.id where u.id = 123'
let rows = await this.orm.query(selectSql)

//总记录条数
let countSql = 'select count(1) as total from comment as c left join user as u on c.user_id = u.id where u.id = 123'
let count = await this.orm.query(countSql)

//返回结果
return {'rows': rows, 'count': count[0].total}

随机函数

//随机获取指定条记录
//重点语句 this.orm.sequelize.random()
async randomPageDataByType (type, offset, limit) {

        return await this.orm[this.model].findAll({
            attributes: this.list_attrs,
            where: {'type': type, 'state': 1},
            order: this.orm.sequelize.random(),
            offset: offset,
            limit: limit
        });
    }