egg-sequelize关联查询三种方法

1.关联查询

egg-sequelize关联查询有在service中定义、在modal中定义和使用原始查询3种方法

1.1.在Service里定义关联查询

import { Service } from 'egg';

export default class sroceService extends Service {
    /*
     * 分页查询
     */
    public async findSorce(query, page) {
        let { ctx } = this
        ctx.model.TSorce.belongsTo(ctx.model.TUser, {
            foreignKey: "user_id",
            targetKey: "id",
            constraints: false
        })
        return await ctx.model.TSorce.findAndCountAll({
            where: query,
            offset: page.pageNumber - 1,
            limit: +page.pageSize,
            include: [
                {
                    model: ctx.model.TUser,
                    attributes: ['username', "id"],
                }
            ]
        })
    }
}

1.2. 在Modal里定义

  Model.associate = function () {
    app.model.TSorce.belongsTo(app.model.TUser, {
      foreignKey: "user_id",
      targetKey: "id",
      constraints: false
    })
  }

1.3 原始查询

export default class sroceService extends Service {
    /*
     * 分页查询
     */
    public async findSorce(query, page) {
        let { ctx } = this
        if (query.type && query.userid && page.pageNumber) {
            let result: any = {
                rows: [],
                count: null,
            };
            let count: any = await ctx.model.query(
                `SELECT count(t_sorce.id) as count FROM t_sorce left join t_user on t_sorce.user_id = t_user.id  WHERE t_sorce.type = ${query.type} and t_sorce.user_id = '${query.user_id}'`,
                {
                    type: ctx.model.QueryTypes.SELECT,
                    plain: true,
                    raw: true,
                }
            );
            result.count = count.count;
            if (result.count) {
                result.rows = await ctx.model.query(
                    `SELECT t_sorce.* FROM t_sorce left join t_user on t_sorce.user_id = t_user.id  WHERE t_sorce.type = ${query.type} and t_sorce.user_id = '${query.user_id}'
                    limit ${page.pageNumber - 1}, ${page.pageSize}`,
                    {
                        type: ctx.model.QueryTypes.SELECT
                    }
                );
            }
            return result
        }
        return null;
    }
}

2. 其他问题

1.1. 返回结果为嵌套结构

关联查询如果采用上面的方法1和方法2，返回的结果往往是嵌套结构如

[
  {
    "id": 1,
    "user_id": "0003",
    "created_at": "2021-07-16T18:29:23.000Z",
    "description": "",
    "t_user":{
        "username": "张三"
    }
  }
]

如果需要转化成扁平的结构，目前没看到什么好的处理。暂时是对结果进行一次转化

export default class sroceService extends Service {
    /*
     * 分页查询
     */
    public async findSorce(query, page) {
        let { ctx } = this
        ctx.model.TSorce.belongsTo(ctx.model.TUser, {
            foreignKey: "user_id",
            targetKey: "id",
            constraints: false,
        })
        let result = await ctx.model.TSorce.findAndCountAll({
            where: query,
            offset: page.pageNumber - 1,
            limit: +page.pageSize,
            raw: true,//表明返回的数据是json而不是model
            include: [
                {
                    model: ctx.model.TUser,
                    attributes: ['username'],
                }
            ],
        })
        result.rows.forEach((element) => {
            flattenObj(element)
        });
        return result;
}

function flattenObj(values) {
    let keys1 = Object.keys(values);
    keys1.forEach((key) => {
        let name = key.split(".");
        let item = ""
        key.split(".").length && (item = name[name.length - 1]);
        if (!values[item]) {
            values[item] = values[key];
            delete values[key]
        }
    });
    return values;
}

3. 参考

中文文档：https://www.sequelize.com.cn/