如何在Sequelize的WHERE查询中包含关联DiscordUser的username字段？

我用Sequelize的findAndCountAll方法查询User模型，已经通过include关联了别名为discord_user的DiscordUser模型（关联关系是User.belongsTo(DiscordUser, {as: 'discord_user'})）。当前WHERE的Op.or里已经有email和full_name的LIKE查询，现在想加上对DiscordUser.username字段的LIKE查询，该怎么实现？

现有代码：

const users = await User.findAndCountAll({
    limit: 20,
    offset: 20 * page,
    attributes: {
        include: [[literal('`discord_user.username`'), 'username']]
    },
    order: [['email', 'ASC']],
    where: {
        [Op.or]: [
            {email: {[Op.like]: '%' + query + '%'}},
            {full_name: {[Op.like]: '%' + query + '%'}},
        ]
    },
    include: [
        {model: Licence},
        {
            model: DiscordUser,
            as: "discord_user",
        }
    ]
});

期望效果的伪代码：

where: {
    [Op.or]: [
        {email: {[Op.like]: '%' + query + '%'}},
        {full_name: {[Op.like]: '%' + query + '%'}},
        DiscordUser.username LIKE query
    ]
}

要在主模型的Op.or条件中加入关联模型字段的查询，不能直接像主模型字段那样写，需要借助Sequelize的工具函数来指定关联表的字段。这里提供两种可靠实现方式：

方式一：使用literal直接编写SQL片段 #

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

const users = await User.findAndCountAll({
    limit: 20,
    offset: 20 * page,
    attributes: {
        include: [[literal('`discord_user.username`'), 'username']]
    },
    order: [['email', 'ASC']],
    where: {
        [Op.or]: [
            {email: {[Op.like]: `%${query}%`}},
            {full_name: {[Op.like]: `%${query}%`}},
            // 新增关联表username的LIKE查询
            literal(`\`discord_user\`.\`username\` LIKE '%${query}%'`)
        ]
    },
    include: [
        {model: Licence},
        {
            model: DiscordUser,
            as: "discord_user",
            required: false // 保留未关联DiscordUser的用户，按需设置
        }
    ]
});

方式二：使用col+where（更安全，避免SQL注入） #

如果query来自用户输入，推荐这种方式，它会自动处理参数转义：

const { Op, where, col } = require('sequelize');

const users = await User.findAndCountAll({
    limit: 20,
    offset: 20 * page,
    attributes: {
        include: [[col('discord_user.username'), 'username']] // 这里也改用col更规范
    },
    order: [['email', 'ASC']],
    where: {
        [Op.or]: [
            {email: {[Op.like]: `%${query}%`}},
            {full_name: {[Op.like]: `%${query}%`}},
            // 新增关联表字段查询
            where(col('discord_user.username'), {
                [Op.like]: `%${query}%`
            })
        ]
    },
    include: [
        {model: Licence},
        {
            model: DiscordUser,
            as: "discord_user",
            required: false
        }
    ]
});

关键注意事项 #

• required: false：如果不设置，Sequelize默认会用内连接，过滤掉所有未关联DiscordUser的用户；如果业务要求用户必须关联DiscordUser，可以设为true。
• 模板字符串%${query}%比字符串拼接更简洁易读，效果完全一致。
• 优先使用col+where的方式处理用户输入的查询参数，防止SQL注入风险。

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