Virtual column enhancement

[LeonardoZivieri]

Issue Creation Checklist

[X] I have read the contribution guidelines

Feature Description

Is your feature request related to a problem? Please describe.

No, this feature will not resolve a problem, but will enable you to filter the result based on a virtualized column.

Describe the solution you'd like

When you init a model with a virtual column, you can't use this column to filter the model, using where clause because its virtual.

For example: I have the model ReportSchedule, it store the information that users will recieve, see above:

const { Sequelize , Model, DataTypes } = require("sequelize");

class ReportSchedule extends Model {
  /**
   * @param {import("sequelize/types").ModelAttributes} options
   */
  static init(options) {
    super.init(
      {
        Id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
        SendTime: { type: DataTypes.TIME, allowNull: false },
        toSendDateTime: {
          type: Sequelize.VIRTUAL(
            Sequelize.DATE,
            Sequelize.literal(`(cast(cast(getdate() as date) as datetime)) + (cast(SendTime as datetime)) as toSendDateTime`)
          )
        }
      },
      {
        ...options,
        tableName: "ReportSchedule",
        createdAt: 'CreatedAt',
        updatedAt: 'UpdatedAt',
      }
    );
  }
}

module.exports = ReportSchedule;

But I can't use this value in a where, like this.

const reportsToPrepareQuery = await ReportSchedule.findAndCountAll({
  where: {
    UpdatedAt: {
      [Op.lt]: Sequelize.col('toSendDateTime')
    },
    toSendDateTime: {
      [Op.between]: [
        Sequelize.literal(getDatetimeFromDateObject(lastCheckedValues)),
        Sequelize.literal(getDatetimeFromDateObject(add(new Date(), { minutes: 10 }))),
      ]
    }
  }
})

Why should this be in Sequelize

Should be in Sequelize because is in the construction of model, and this virtual type is native of Sequelize.

Describe alternatives/workarounds you've considered

Maybe, in the construction of queries, in databases that don't permit use virtual columns in WHERE, switch the table to a subquery, like:

SELECT ...... FROM ( SELECT *, (cast(cast(getdate() as date) as datetime)) + (cast(SendTime as datetime)) as [toSendDateTime] FROM [ReportSchedule] ) as [ReportSchedule] WHERE .........

instead of

SELECT ......, (cast(cast(getdate() as date) as datetime)) + (cast(SendTime as datetime)) as [toSendDateTime] FROM [ReportSchedule] WHERE .........

Is this feature dialect-specific?

( Honestly, I don't know, but in SQL Server and MySQL, that I use, don't permit this usage of virtual columns in WHERE, only in Having or with a subquery )

• No. This feature is relevant to Sequelize as a whole.
• Yes. This feature only applies to the following dialect(s): XXX, YYY, ZZZ

Would you be willing to implement this feature by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[ephys]

PR welcome