I have a User model and Timeoffs model with one to many relation. I want get all the users that doesn't have timeoffs in the specified date. Right now in the code I'm doing the exact opposite of this - getting only the users with timeoffs in the given date. How can I do this in right way? Is it possible to just invert include in that part or should I write a more complex query?
getQuestionHourUsersByDate (date, timeId) {
return UsersModel.findAll({
where: {
[USERS.COLUMNS.QH_STATUS]: true,
},
include: [
{
model: TimeoffsModel,
where: {
[Operator.and]: {
[TIMEOFFS.COLUMNS.PROCEED_STATUS]: true,
[TIMEOFFS.COLUMNS.STATUS]: TimeOffEnums.TIMEOFF_REQUEST_STATUS.APPROVED,
},
[Operator.and]: {
[TIMEOFFS.COLUMNS.START_DATE]: {
[Operator.lte]: date,
},
[TIMEOFFS.COLUMNS.END_DATE]: {
[Operator.gte]: date,
},
},
},
},
{
model: RolesModel,
where: {
[ROLES.COLUMNS.ROLE_GROUP_ID]: 1,
},
through: {
attributes: [],
where: {
[USER_ROLES.COLUMNS.STATE]: true,
},
},
},
{
model: ResponsibilitiesModel,
attributes: [
RESPONSIBILITIES.COLUMNS.NAME,
RESPONSIBILITIES.COLUMNS.RESPONSIBILITY_GROUP_ID,
RESPONSIBILITIES.COLUMNS.COLOR_CODE,
],
where: {
[RESPONSIBILITIES.COLUMNS.RESPONSIBILITY_GROUP_ID]: 2,
},
required: false,
through: {
attributes: [],
where: {
[USER_RESPONSIBILITIES.COLUMNS.ACTIVE]: true,
},
},
},
{
model: LanguagesModel,
attributes: [
LANGUAGES.COLUMNS.CODE,
LANGUAGES.COLUMNS.NAME,
],
},
{
model: UserCalendarsModel,
include: [
{
model: CalendarsModel,
where: {
[CALENDARS.COLUMNS.START_DATE]: date,
[CALENDARS.COLUMNS.ACTIVE]: true,
},
include: [
{
model: QuestionHourSlotsModel,
where: {
[QUESTION_HOUR_SLOTS.COLUMNS.TIME_ID]: timeId,
},
},
],
},
],
},
],
});
}
Unfortunately, I should use Sequelize.literal
in where
option to add a subquery condition with NOT EXISTS
like this:
where: Sequelize.where(Sequelize.literal('NOT EXISTS (SELECT 1 FROM ... WHERE startDate <= @date and endDate >= @date)'), '=', true)
and also to pass a specified date you add to indicate bind
option along with main query options like this:
where: {
[USERS.COLUMNS.QH_STATUS]: true,
},
bind: {
date
}