Unknown column while executing where conditions Sequelize subquery field

Question

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.

to filter aggregate you need to use having instead of where — Lukasz Szozda
– Lukasz Szozda, Commented Dec 23, 2019 at 10:46
@LukaszSzozda thanks for helping me out. So we should using having clause. Damn it! mysql should give error message like somehting else. You can post your answer. — bring2dip
– bring2dip, Commented Dec 23, 2019 at 11:01
Create your query in sql first and verify its working. Then write it in sequelize and check if sequelize is generating the same query (semantically) or not. It will help you in debugging. — Shaharyar
– Shaharyar, Commented Dec 23, 2019 at 11:03

Lukasz Szozda · Accepted Answer · 2019-12-23 11:02:34Z

2

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

answered Dec 23, 2019 at 11:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unknown column while executing where conditions Sequelize subquery field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related