Sequelize:父 where 子句和子 where 子句之间的 OR [英] Sequelize: OR between parent where clause and child where clause

问题描述

我有 2 个模型:

const User = sequelize.define('User', {
    email: {
        type: DataTypes.STRING,
    },
    password: {
        type: DataTypes.STRING,
    },
});
User.associate = (models) => {
    User.hasOne(models.Profile, {
        foreignKey: {
            name: 'user_id',
        },
    });
};

const Profile = sequelize.define('Profile', {
    name: {
        type: DataTypes.STRING,
    },
    avatar: {
        type: DataTypes.STRING,
    },
}, {
    tableName: 'profiles',
    freezeTableName: true,
    timestamps: false,
});

Profile.associate = (models) => {
    Profile.belongsTo(models.User, {
        foreignKey: {
            name: 'user_id',
        },
    });
};

我想获取电子邮件地址或名称与特定条件匹配的所有用户.比如:

I would like to get all users where the email address OR the name matches a certain condition. Something like:

User
    .all({
        where: {
            email: {
                $like: filter
            },
        },
        include: [{
            model: Profile,
            where: {
                name: {
                    $like: filter
                },
            },
        }],
    })
    .then(users => res.status(200).send(users))
    .catch(error => {
        return res.sendStatus(500);
    });

但它会返回 user.email AND profile.name 匹配条件的所有用户.我想在两个 where 子句之间有 OR.

but it returns all users where user.email AND profile.name matches the condition. I would like to have OR between the 2 where clause.

有可能吗?

注意:我正在使用 Sequelize 4.0.0.

Note: I'm using Sequelize 4.0.0.

更新:如果其他人对此感到困惑，解决方案是:

Update: In case of anybody else struggles with this, the solution is:

User
    .all({
        where: {
            $or: {
                email: {
                    $like: filter
                },
                '$Profile.name$': {
                    $like: filter
                }
            }
        },
        include: [{
            model: Profile,
        }],
    })
    .then(users => res.status(200).send(users))
    .catch(error => {
        return res.sendStatus(500);
    });

推荐答案

如果其他人正在寻找这个，我是这样解决的:

In case if anyone else is looking for this, here is how I managed to solve it:

User
    .all({
        where: {
            $or: {
                email: {
                    $like: filter
                },
                '$Profile.name$': {
                    $like: filter
                }
            }
        },
        include: [{
            model: Profile,
        }],
    })
    .then(users => res.status(200).send(users))
    .catch(error => {
        return res.sendStatus(500);
    });

感谢@Ninja Coding 确认解决方案.

Thanks @Ninja Coding for confirming the solution.

这篇关于Sequelize:父 where 子句和子 where 子句之间的 OR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！