kukukk kukukk - 1 month ago 8
Node.js Question

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

Answer Source

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.