Description:

I’m encountering an issue in a Sequelize project when trying to use sequelize.col() to select fields from associated models. The setup involves two main models, Student and User, where Student has a createdBy field referencing a User. Additionally, Student has a many-to-many relationship with StudyYear through a join table called StudentStudyYears.

My goal is to fetch the name of the creator (User.name) and alias it as creatorName during a findAndCountAll query. However, I’m getting an “unknown column” error when including the studyYears association.

Models and Associations

Student Model:

const Student = sequelize.define('Student', {
  id: {
    type: DataTypes.UUID,
    primaryKey: true,
  },
  code: DataTypes.STRING,
  name: DataTypes.STRING,
  lastname: DataTypes.STRING,
  createdBy: {
    type: DataTypes.UUID,
    allowNull: true,
  },
  // other attributes...
});

Student.belongsTo(User, { foreignKey: 'createdBy', as: 'creator' });
Student.belongsToMany(StudyYear, {
  through: StudentStudyYears,
  foreignKey: 'studentId',
  as: 'studyYears',
});

User Model:

const User = sequelize.define('User', {
  id: {
    type: DataTypes.UUID,
    primaryKey: true,
  },
  name: DataTypes.STRING,
  // other attributes...
});

StudyYear Model:

const StudyYear = sequelize.define('StudyYear', {
  id: {
    type: DataTypes.UUID,
    primaryKey: true,
  },
  year: DataTypes.STRING,
  // other attributes...
});

StudyYear.belongsToMany(Student, {
  through: StudentStudyYears,
  foreignKey: 'studyYearId',
  as: 'students',
});

Query

Here’s the query I’m trying to execute to fetch students along with specific creator data and study years:

const { count, rows } = await Student.findAndCountAll({
  attributes: [
    'id',
    'code',
    'name',
    'lastname',
    'avatar',
    'email',
    'dni',
    'phone',
    'dateOfBirth',
    'gender',
    'address',
    'isActive',
    'isBanned',
    [sequelize.col('creator.name'), 'creatorName'],
  ],
  include: [
    {
      model: User,
      as: 'creator',
      attributes: ['name', 'avatar'],
    },
    {
      model: StudyYear,
      as: 'studyYears',
      attributes: ['id', 'year'],
      through: { attributes: [] },
    },
  ],
  paranoid: true,
  where: {},
  order: [],
  offset: 0,
  limit: 10,
});

Error

The following error occurs when the query is executed:

(conn:488, no: 1054, SQLState: 42S22) Unknown column 'creator.name' in 'field list'

This error only appears when studyYears is included in the query. If I remove the studyYears part from the include array, the query runs successfully and returns the expected results.

What I’ve Tried

• Confirming that associations are correctly defined.
• Ensuring the columns and aliases in the query are correct.
• Verifying that the tables and columns exist in the database and that relationships are properly set up.

Question

Why does sequelize.col('creator.name') fail to find the column ‘creator.name’ when studyYears is included? What might be causing this conflict, and how can I successfully fetch the creator’s name along with the study years in the same query?

Any help or guidance would be greatly appreciated!