Foreign key appears twice in generated query using association and include

Viewed 26

I am trying to get all users associated to timeoff. This is the Timeoff model:

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Timeoff extends Model {
    static associate(models) {
      // define association here
      Timeoff.belongsTo(models.User, {
        foreignKey: "userId",
      });
    }
  }
  Timeoff.init(
    {
      userId: DataTypes.INTEGER,
      date:DataTypes.DATEONLY
    },
    {
      sequelize,
      modelName: "Timeoff",
    }
  );

  Timeoff.findUsers = async (startDate, endDate) => {
    try {
      const object = await sequelize.models.Timeoff.findAll({
        include: {
          model: sequelize.models.User,
        },
      });

      return Promise.resolve(object);
    } catch (error) {
      return Promise.reject(error);
    }
  };

  return Timeoff;
};

The query complains: error: column Timeoff.UserId does not exist

The generated query has the foreign key twice, once as 'userId' and again as 'UserId' capitalized:

SELECT 
  "Timeoff"."id", 
  "Timeoff"."userId", 
...
  "Timeoff"."UserId", 

Any idea what causes this and how to have only the foreign key 'userId' in the generated query? Thanks.

UPDATE

This in the other model, User, makes it fail:

static associate(models) {
  User.hasMany(models.Timeoff);
}

But this in User model makes it succeed:

static associate(models) {
  User.hasMany(models.Timeoff, {
    as: "timeoffs",
    foreignKey: "userId",
  });

As to why, I do not know yet.

1 Answers

You should indicate the same foreignKey options in both paired associations in order to make working associations in queries because Sequelize takes into account both associations.

Related