CODEWITHSUNDEEP
postgresqlsequelize.js
TheInformedMan
TheInformedMan

Reputation: 125

Sequelize query where record does not exist in related table or does exist in related table with condition

I'm trying to write a sequelize query which includes a relation from different table with hasMany relation. I want my query to return if some column in related table has matches my condition or there is not related row in the related table with foreign key from my main table.

Think about two different models, main one, which is folders, has id, name etc. columns and related table, which is folderOwners has id, folderId and groupId columns which shows which folders is owned by which groups. So, folders can have multiple owner groups.

include: [{
          association: "owners",
          required: true,
          where: {
            groupId: {[Op.in]: userGroups.map(group => group.id)}
          }
        }]

I can get the folders which owned by one of the groups that user is in but I want to get all folders if there is no row in related table, which means folder is not owned by anyone. I've tried to change required to false and use Op.or for where like below

         required: true,
          where: {
          [Op.or]: [
            {groupId: {[Op.in]: userGroups.map(group => group.id)}},
            {groupId: {[Op.eq]: null}}
              ],
          }

So, are there any ways to achieve what I want?

Upvotes: 3

Views: 7175

Answers (2)

Oleksandr Hrin
Oleksandr Hrin

Reputation: 896

You can reference to nested model's column from parent model's "where" object using syntax like this: "$AssociatedTableName.columnName$"

Folder.findAll({
  where: {
    [Op.or]: [
      { '$Group.id$': null },
      {
        '$Group.id$': {
          [Op.in]: [ids],
        },
      },
    ],
  },
  include: [
    {
      model: Group,
      as: 'Group',
      required: false,
    },
  ],
});

Upvotes: 2

hamdi
hamdi

Reputation: 71

If I understood correctly your problem, try this code :

    Folder.findAll({include: [{model: Group, required: false}])

it will get all the folders with and without Groups

Upvotes: 5

Related Questions