Node.js中Sequelize间接表关联查询Assignment的实现及报错解决

给出的Sequelize模型定义如下：

const Client = db.define('clients', {
    client_id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    }
});

Client.hasMany(Project, {foreignKey: 'project_client_id'});


const Project = db.define('projects', {
    project_id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    project_client_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
    }
});

Project.hasMany(Assignment, {foreignKey: 'assignment_project_id'});

const Assignment = db.define('assignments', {
    assignment_id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    assignment_status: {
        type: Sequelize.ENUM,
        values: ['Started', 'In-Progress', 'Closed'],
        allowNull: false,
        defaultValue: 'Started'
    },
    assignment_project_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
    }
});

需求为：查询所有Assignment数据，查询参数client_id、project_id、assignment_id、assignment_status均为可选，未传入任何参数则返回全部数据。尝试在Assignment.findAll中直接关联Client时触发错误：Error: SequelizeEagerLoadingError: clients is not associated to assignments!，请问如何编写正确的Assignment.findAll代码？

出现该错误的核心原因是Assignment与Client无直接关联关系，二者通过Project间接关联，需补充反向关联并通过嵌套关联查询实现需求。

1. 补充反向关联 #

Sequelize需要完整的关联定义才能支持跨模型嵌套查询，需添加以下反向关联代码：

// 定义Project属于Client的反向关联
Project.belongsTo(Client, { foreignKey: 'project_client_id' });

// 定义Assignment属于Project的反向关联
Assignment.belongsTo(Project, { foreignKey: 'assignment_project_id' });

2. 编写带动态条件的查询代码 #

通过动态构建查询条件，并嵌套关联Project和Client，即可实现支持所有可选参数的查询：

async function fetchAssignments(queryParams) {
    // 初始化查询条件对象
    const assignmentWhere = {};

    // 按传入参数添加条件
    if (queryParams.assignment_id) {
        assignmentWhere.assignment_id = queryParams.assignment_id;
    }
    if (queryParams.assignment_status) {
        assignmentWhere.assignment_status = queryParams.assignment_status;
    }
    if (queryParams.project_id) {
        assignmentWhere.assignment_project_id = queryParams.project_id;
    }

    return await Assignment.findAll({
        where: assignmentWhere,
        include: [
            {
                model: Project,
                // 若传入client_id，在Project层过滤对应客户
                where: queryParams.client_id ? { project_client_id: queryParams.client_id } : {},
                include: [Client]
            }
        ]
    });
}

关键说明 #

• 当需要通过client_id过滤时，需在关联的Project模型上添加where条件，因为Assignment与Client无直接外键关联。
• 所有参数均为可选，未传入的参数不会被加入查询条件，默认返回全部Assignment数据（含关联的Project和Client信息）。
• 补充反向belongsTo关联是实现嵌套查询的必要前提，否则Sequelize无法识别跨模型的关联关系。

内容的提问来源于stack exchange，提问作者SVGK Raju