CODEWITHSUNDEEP
mysqlnode.jssequelize.js
bring2dip
bring2dip

Reputation: 885

Unknown column while executing where conditions Sequelize subquery field

I was trying to create a query that finds products with price less than or equals to avgPrice.

Below is the sample query.

await Product.findAll({
    attributes: [
      'id', 'name', 'price',
      [sequelize.literal(`SELECT AVG(price) from Products`), 'avgPrice']
    ],
    where: {
      price: {
        [Sequelize.Op.lte]: sequelize.col('avgPrice')
      }
    }

But it gives the following error.

Unknown column 'Products.avgPrice' in 'where clause'

Also I tried to copy the query generated by sequelize and execute it manually in mysql but it gives the same error.

Isn't this how we run the query?

Edit: I changed the query requirements from avgPrice < 100 to price < avgPrice. However, the problem in the question is the same.

Upvotes: 1

Views: 1067

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

If you want to filter based on aggregated column you need to change WHERE clause to HAVING.

Upvotes: 2

Related Questions