How to set common where param to all models in query, in sequelize

Question

I'm trying to realize the query, where I can find all records, which at least one of the attributes includes the text, user send with request, for that I use where, but it can search, as I understand, in only own model, but i need it to search it in parent model and in associated filds together, not apart.

There is my models:

const Picture = sequelize.define<IPictureInstance>('picture', {
   id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
   img: { type: DataTypes.STRING, allowNull: false },
   mainTitle: { type: DataTypes.STRING, allowNull: false },
   description: { type: DataTypes.TEXT }
});

const PictureInfo = sequelize.define<IPictureInfoInstance>('pictureInfo', {
   id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
   title: { type: DataTypes.STRING, allowNull: false },
   description: { type: DataTypes.STRING, allowNull: false }
});

const PictureTag = sequelize.define<IPictureTagInstance>('pictureTag', {
   id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
   text: { type: DataTypes.TEXT, allowNull: false }
});

const PicturesTags = sequelize.define<IPicturesTagsInstance>('picturesTags', {
   id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }
});

And their associations:

Picture.belongsToMany(PictureTag, { through: PicturesTags, as: "tags", onDelete: 'cascade' });
PictureTag.belongsToMany(Picture, { through: PicturesTags, as: "pictures", onDelete: 'cascade' 
});

Picture.hasMany(PictureInfo, { onDelete: "cascade" });
PictureInfo.belongsTo(Picture);

That's what I tried to do:

  static async getPictures(query: string | undefined) {
const pictures = await models.Picture.findAll({
  where: {
    [Op.or]: {
      mainTitle: { [Op.iRegexp]: `${query}` },
      description: { [Op.iRegexp]: `${query}` },
    },
  },
  include: [
    {
      model: models.PictureInfo,
      as: "pictureInfos",
      where: {
        [Op.or]: {
          title: { [Op.iRegexp]: `${query}` },
          description: { [Op.iRegexp]: `${query}` }
        }
      },
      required: false
    },
    {
      model: models.PictureTag,
      as: "tags",
      attributes: ["id", "text"],
      where: { text: { [Op.iRegexp]: `${query}` } },
      through: {
        attributes: [],
      },
      required: false
    }

  ],

});
  return pictures;
}

But in this case, when it can't find records in first where param it returns an empty array, I understand it, but it isn't a behavior I need.

I need to check every attribute together.

So, if user send query=cat, it will check mainTitle and description, if there is nothing, it will check associated pictureInfos fields and after, if there is nothing, check pictureTags associated fields, that's what I need, will be grateful for the help.

Answer 1

My solution:

static async getPictures(query: string | undefined) {

const whereStatement = {
  [Op.or]: {
    mainTitle: { [Op.iRegexp]: `${query}` },
    description: { [Op.iRegexp]: `${query}` },
    "$tags.text$": { [Op.iRegexp]: `${query}` },
    "$pictureInfos.title$": { [Op.iRegexp]: `${query}` },
    "$pictureInfos.description$": { [Op.iRegexp]: `${query}` },
  }
};

const pictures = await models.Picture.findAll({
  where: whereStatement,
  include: [
    {
      model: models.PictureInfo,
      as: "pictureInfos",
    },
    {
      model: models.PictureTag,
      as: "tags",
    }

  ],
});

return pictures;
}

But I'm still confused with those $ symbols, what they are for, I couldn't find the answer in documentation?

Link to the Sequelize documentation, to confirm my answer