kukukk
kukukk

Reputation: 138

Sequelize: OR between parent where clause and child where clause

I have 2 models:

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);
    });

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.

Is it possible?

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);
    });

Upvotes: 3

Views: 3018

Answers (1)

kukukk
kukukk

Reputation: 138

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);
    });

Thanks @Ninja Coding for confirming the solution.

Upvotes: 3

Related Questions