sequelize 关联查询问题

Question

目前遇到的问题是通过sequelize 进行关联查询后需要的问题是关联查询结果第一次查询没问题，再查询一次就会报错。
1、两个关联的模型
（1）菜单模型

'use strict';

module.exports = app => {
    const { STRING, INTEGER, DATE } = app.Sequelize;
    let table_name = 'qt_admin_menu';

    const AdminMenu = app.model.define(table_name, {
        menu_id: { 
            type: 'VARCHAR(32)',
            allowNull: false,
            defaultValue: '',
            primaryKey: true 
        },
        parent_id: { 
            type: 'VARCHAR(32)',
            allowNull: false,
            defaultValue: '0',
            primaryKey: false 
        },
        pk: { 
            type: 'VARCHAR(32)',
            allowNull: false,
            defaultValue: '',
            primaryKey: false 
        },
         menu_type: {
            type: 'TINYINT(3) UNSIGNED',
            allowNull: true,
            defaultValue: '1',
            primaryKey: false
        },
        top_menu: {
            type: 'INT(1) UNSIGNED',
            allowNull: true,
            defaultValue: '2',
            primaryKey: false
        },
        status: {
            type: 'TINYINT(3) UNSIGNED',
            allowNull: true,
            defaultValue: '1',
            primaryKey: false
        },
        list_order: {
            type: 'TINYINT(5) UNSIGNED',
            allowNull: true,
            defaultValue: '1',
            primaryKey: false
        },
        menu_app: {
            type: 'VARCHAR(15)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        controller: {
            type: 'VARCHAR(30)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        action: {
            type: 'VARCHAR(30)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        url_method: {
            type: 'VARCHAR(10)',
            allowNull: true,
            defaultValue: 'get',
            primaryKey: false
        },
        menu_url: {
            type: 'VARCHAR(200)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        param: {
            type: 'VARCHAR(50)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        menu_name: {
            type: 'VARCHAR(30)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        icon: {
            type: 'VARCHAR(20)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        remark: {
            type: 'VARCHAR(255)',
            allowNull: true,
            defaultValue: '',
            primaryKey: false
        },
        isdelete: {
            type: 'INT(1)',
            allowNull: true,
            defaultValue: '2',
            primaryKey: false
        }
    });
    // AdminMenu.removeAttribute('id');
    return AdminMenu;
};

（2）角色菜单关联表模型

'use strict';

module.exports = app => {
    const { STRING, INTEGER, DATE } = app.Sequelize;
    let table_name = 'qt_admin_role_menu';

    const AdminRoleMenu = app.model.define(table_name, {
        id: {
            type: 'INT(11)',
            allowNull: false,
            defaultValue: null,
            primaryKey: true,
            autoIncrement: true
        },
        role_id: {
            type: 'INT(11)',
            allowNull: false,
            primaryKey: false 
        },
        menu_id: {
            type: 'VARCHAR(32)',
            allowNull: false,
            defaultValue: '',
            primaryKey: false 
        }
    });

    return AdminRoleMenu;
};

2、service 里面的查询

'use strict';
const Service = require('egg').Service;

class MenuService extends Service {
    // 默认不需要提供构造函数。
    constructor(ctx) {
        //如果需要在构造函数做一些处理，一定要有这句话，才能保证后面 `this.ctx`的使用。
        super(ctx); 
        // 就可以直接通过 this.ctx 获取 ctx 了
        // 还可以直接通过 this.app 获取 app 了
        this.table_name = 'qt_admin_menu';
        // 加载model
        this.adminMenuModel = ctx.model.AdminMenu;
        this.adminRoleMenuModel = ctx.model.AdminRoleMenu;
        // 定义 hasMany 关联
        this.adminMenuModel.hasMany(this.adminRoleMenuModel, {
            as: 'u',
            through: null,
            foreignKey: 'menu_id'
        })
        // this.adminRoleMenuModel.belongsToMany(this.adminMenuModel, {
        //     // as: 'adminMenuModel',
        //     foreignKey: 'menu_id'
        // });
    }
    async getRoleMenu(rid){
        // [this.app.Sequelize.fn('if', 'adminRoleMenuModel.menu_id is null', 'false', 'true'), 'selected']
        const result = await this.adminMenuModel.findAll({
            raw: true,
            attributes: ['menu_id', 'parent_id', 'menu_name', 'icon', [this.app.Sequelize.col('u.id'), 'selected']],
            include: [{
                attributes: [],
                model: this.adminRoleMenuModel,
                as: 'u',
                where: {
                    role_id: rid,
                },
                required: false
            }],
            order: [
                ['top_menu', 'ASC'],
                ['list_order', 'ASC']
            ]
        })
        // .then(rows => rows && rows.map(r => r.toJSON()));
        // console.log(result);
        //const result = await this.app.mysql.query('select a.*, b.menu_id as selectedex, if(b.menu_id is null,"false","true") as selected from qt_admin_menu a left join qt_admin_role_menu b on a.menu_id = b.menu_id and b.role_id = ? ORDER BY a.top_menu, a.list_order', [rid]);
        return result;
    }

}
module.exports = MenuService;

3、查询结果
修改完文件之后，执行请求，查询结果正常，打印SQL也正常

SELECT `qt_admin_menu`.`menu_id`, `qt_admin_menu`.`parent_id`, `qt_admin_menu`.`menu_name`, `qt_admin_menu`.`icon`, `u`.`id` AS `selected` FROM `qt_admin_menu` AS `qt_admin_menu` LEFT OUTER JOIN `qt_admin_role_menu` AS `u` ON `qt_admin_menu`.`menu_id` = `u`.`menu_id` AND `u`.`role_id` = '3' ORDER BY `qt_admin_menu`.`top_menu` ASC, `qt_admin_menu`.`list_order` ASC;

结果数据：

 { menu_id: 'ef535c8a938665d1d0641a4e86ee83ed',
    parent_id: 'a8e66f7be77c9bf71a2511331ac6ba32',
    menu_name: '列表组',
    icon: null,
    selected: null }

但是再执行一次就会报错了
You have used the alias u in two separate associations. Aliased associations must have unique aliases.

网上也找不到相应的问题，希望大神帮忙解决一下。非常感谢！

Answer 1

确保 as 唯一。https://github.com/sequelize/...

Answer 2

service 里面的关联查询参考

const Categories = Product.hasMany(Tag, { as: 'categories' });

Product.create({
  id: 1,
  title: 'Chair',
  categories: [
    { id: 1, name: 'Alpha' },
    { id: 2, name: 'Beta' }
  ]
}, {
  include: [{
    model: Categories,
    as: 'categories'
  }]
})

Answer 3

感谢大佬们的帮助，基本指导问题的原因了，在指定别名的时候一定要把单数和复数形式都指定了 要不然就会报错了

// 定义 hasMany 关联
        this.adminMenuModel.hasMany(this.adminRoleMenuModel, {
            as: { 
                singular: 'adminRoleMenuModel', 
                plural: 'adminRoleMenuModels'
            },
            foreignKey: 'menu_id'
        })

感觉这个orm设计的蛋疼的地方比较多啊