unknown column in field list sequelize

Question

I'm trying to perform the following query using Sequelize:

db.Post.findAll({
      include: [
        {
          model: db.User,
          as: 'Boosters',
          where: {id: {[Op.in]: a_set_of_ids }}
        },
        {
          model: db.Assessment,
          as: 'PostAssessments',
          where: {UserId: {[Op.in]: another_set_of_ids}}
        }
      ],
      attributes: [[db.sequelize.fn('AVG', db.sequelize.col('Assessments.rating')), 'average']],
      where: {
        average: 1
      },
      group: ['id'],
      limit: 20
    })

But I run to this error: "ER_BAD_FIELD_ERROR". Unknown column 'Assessments.rating' in 'field list', although I do have table "Assessments" in the database and "rating" is a column in that table.

My Post model looks like this:

const Post = sequelize.define('Post', {
title: DataTypes.TEXT('long'),
description: DataTypes.TEXT('long'),
body: DataTypes.TEXT('long')
  }, {
  timestamps: false
    });

  Post.associate = function (models) {
    models.Post.belongsToMany(models.User, {as: 'Boosters', through: 'UserPostBoosts' });
    models.Post.hasMany(models.Assessment, {as: 'PostAssessments'});
  };

What am I doing wrong?

Farnaz Jahanbakhsh Farnaz Jahanbakhsh · Accepted Answer · 2018-05-22T19:14:08

It seems like this problem surfaces when we have a limit in a find query where associated models are included (the above error doesn't show up when we drop the limit from the query). To solve that, we can pass an option subQuery: false to the find. (https://github.com/sequelize/sequelize/issues/4146)

This is the correct query in case anyone comes across the same problem:

db.Post.findAll({
  subQuery: false,
  include: [
    {
      model: db.User,
      as: 'Boosters',
      where: {id: {[Op.in]: a_set_of_ids }}
     }
    ,{
      model: db.Assessment,
      as: 'PostAssessments',
      where: {UserId: {[Op.in]: another_set_of_ids}}
    }
  ],
  having: db.sequelize.where(db.sequelize.fn('AVG', db.sequelize.col('PostAssessments.rating')), {
       [Op.eq]: 1,
     }),
  limit: 20,
  offset: 2,
  group: ['Post.id', 'Boosters.id', 'PostAssessments.id']
})

unknown column in field list sequelize

2 Answers