calling functions on columns in where param

[evangow]

I've been trying to figure this out for 2 days, and couldn't find it anywhere in the documentation, so I think this is a feature request. I think this might require a breaking change though, so maybe it would be good for v5?

What I'd like to be able to do

I'd like to be able to be able to call a function on a column from the "where" options object. I'd prefer not to use the sequelize.where function as I am building up the sql where object from multiple objects being passed in.

As an example, I have a table with a date range type column, and I'd like to be able query by the UPPER(dateRange) to see all of the past events that have ended.

In SQL, this would be

SELECT * FROM "test" WHERE UPPER("dateRange") <= NOW();

In Sequelize, this might be something like

const test = sequelize.define('test', {
    id: {
        primaryKey: true,
        type: Sequelize.UUID
    },
    dateRange: {
        type: DataTypes.RANGE(DataTypes.Date),
        allowNull: false
    }
});

test.sync().then(function () {
    test.findAll({
        dateRange: {
          [models.Sequelize.Op.lt]: models.sequelize.fn("NOW")},
          // could also simply be - columnFunction: "UPPER"
          columnFunction: models.sequelize.fn("dateRange", "UPPER")
        }
    })
});

[sushantdhiman]

Already possible #9833 (comment)

[evangow]

I swear I tried using the sequelize.where function before, and it ended up spitting out an object instead of a sql string before. Well, sorry to have bothered you!

For anyone who runs across this same issue, here's what I did.

test.findAll({
  dateRange: sequelize.where(
    sequelize.fn("UPPER", sequelize.col("dateRange")),
    "<",
    sequelize.fn("NOW")
  )
})
// SELECT * FROM test WHERE UPPER("dateRange") < NOW();

It might be worth add this more complex use case to the "querying" section of the docs. None of the example shown in the docs (like the one below for ease of reference) indicate that you can call the sequelize.where function on a specific field inside the options object.

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;