Sequelize include关联查询如何实现多关联表字段的OR嵌套where条件

原EF查询逻辑 #

public async Task<IEnumerable<NotificationAppEntity>> GetPagged(NotificatioAppGetPaggedReq req, Guid userId)
{
    var res = await Context.NotificationApp
        .Where(x => req.Types.Contains(x.NotificationAppTypeId))
        .Include(x => x.CreatedBy).ThenInclude(x => x.UserPics)
        .Include(x => x.Comment)
        .Include(x => x.Task)
        .Include(x => x.Goal)
        .Include(x => x.NotificationAppType)
        .Include(x => x.NotificationAppUserRead)
        .Where(x => x.Task.ProjectId == req.ProjectId.Value || x.Comment.ProjectId == req.ProjectId.Value ||x.Goal.ProjectId == req.ProjectId.Value)
        .OrderByDescending(x => x.CreatedAt)
        .Skip(req.PagingParameter.Skip)
        .Take(req.PagingParameter.Take)
        .ToListAsync();
    return res;
}

问题说明 #

直接在Sequelize的include关联配置中加where条件会默认用AND拼接，无法实现跨多个关联表字段的OR嵌套筛选。

修正后的Sequelize实现代码 #

首先确保已导入Sequelize的操作符：

const { Op } = require('sequelize');

实现代码：

async getPagged(userId, body) {
    const notificationApp = await db.NotificationApp.findAll({
        where: {
            // 对应原EF的req.Types.Contains(x.NotificationAppTypeId)
            NotificationAppTypeId: {
                [Op.in]: body.Types
            },
            // 跨关联表的OR筛选逻辑，注意$关联名.字段$的语法
            [Op.or]: [
                { '$Task.ProjectId$': body.ProjectId },
                { '$Comment.ProjectId$': body.ProjectId },
                { '$Goal.ProjectId$': body.ProjectId }
            ]
        },
        offset: body.PagingParameter.Skip,
        limit: body.PagingParameter.Take,
        include: [
            {
                model: db.User,
                attributes: { exclude: hideAttributes },
                as: 'CreatedBy',
                include: { model: db.UserPic, as: 'UserPics' },
            },
            {
                model: db.Comment,
                // 若要和EF的Include行为一致，关联无数据时仍保留主表记录，添加required: false
                required: false
            },
            {
                model: db.Task,
                required: false
            },
            {
                model: db.Goal,
                required: false
            },
            {
                model: db.NotificationAppType,
            },
            {
                model: db.NotificationAppUserRead,
            },
        ],
        order: [['CreatedAt', 'DESC']],
    });
    return notificationApp;
}

核心实现说明 #

• 使用$关联别名.字段$语法可以在主查询的where条件中直接引用关联表的字段，Sequelize会自动处理SQL拼接时的表别名映射
• 将跨表OR逻辑统一写在主查询的where节点下，而非分散在各个include的where配置中，即可实现和原EF一致的多关联字段OR筛选逻辑
• 注意$包裹的关联别名必须和模型定义关联时配置的as属性完全一致，如果未显式配置as，默认使用关联模型的名称
• required: false配置会强制使用LEFT JOIN，和EF的Include行为完全一致，避免关联不存在时过滤掉主表记录

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