Sequelize 'where' on parent and child when parent hasMany childs

user_12993680 0
Viewed 930

I have 2 models:

class User extends Model {
    static associate(models) {
      User.hasMany(models.Role, {
        foreignKey: 'userId'
      });
    }
  };
  User.init({
    firstname: {
      type: DataTypes.STRING,
    },
    lastname: {
      type: DataTypes.STRING,
    },
    allowedApps: {
      type: DataTypes.ENUM({
        values: Object.keys(PORTALS)
      }),
      allowNull: false
    }
  }, {
    sequelize,
    paranoid: true,
    modelName: 'User',
  });

class Role extends Model {
    static associate(models) {
      Role.BelongsTo(models.User, {
        foreignKey: 'userId'
      });
    }
  };
  Role.init({
    type: {
      type: DataTypes.STRING,
      unique: true
    },
    name: DataTypes.STRING,
    userId: {
      type: DataTypes.INTEGER,
      allowNull: false,
    }
  }, {
    sequelize,
    paranoid: true,
    modelName: 'Role',
  });

I would like to get all users where the firstname OR the role type matches a certain condition. Something like:

User
    .findAndCountAll({
        where: {
            $or: [
         {
            firstname: "John Doe"
         },
         {
            "$Role.type$": "Admin"
         }
       ]
        },
        include: [{
            model: Role,
        }],
    }).limit=10,offset=0
    .then(users => res.status(200).send(users))
    .catch(error => {
        return res.sendStatus(500);
    });

above query giving me error: "SequelizeDatabaseError: Unknown column 'Role.type' in 'field list'"

I want to search through child model when it has one to many relationship having my limit and offset intact.Same query would give me success if user would have HasOne relation with role. This is just an example code of what I try to achieve so please ignore any typos and silly mistakes.

sequelize.js node.js mysql
1 Answers

After some digging:

await User.findAll({
where: {
            $or: [
         {
            firstname: "John Doe"
         },
         {
            "$Role.type$": "Admin"
         }
       ]
        },
  include: {
    model: Role,
    as: 'Role',
    required: false
  }
});

However, it doesn't make logical sense to select Users that have no associated Role (required: false), while querying such Users with a property that exists on Role ($or: $Role.type$). If we set Required = true, then we violate your initial condition

firstname OR the role type matches a certain condition.

The following addresses this problem:

await User.findAll({
  include: {
    model: Role,
    required: false
  }
})
.then(
  users => users
    .filter(user => user?.firstName === "John Doe" || user.role?.type === 
    "Admin");
);
user_149745910
Related