Association with same Model returns dup object

[hpandelo]

Issue

Versions

• "sequelize": "^6.32.1",
• "sequelize-typescript": "^2.1.5",
• "typescript": "^5.1.6"

Issue type

• [x] bug report
• [ ] feature request

Actual behavior

1. A Model (ModelA) is associated with 2 properties of some other model (ModelB)
2. When loading, both associations are retrieved with the same data

Expected behavior

Respective data being retrieved from each association

Steps to reproduce

    const contract = await Contract.findOne({
      where: {
        id,
        [Op.or]: [{ ContractorId: requester?.id }, { ClientId: requester?.id }],
      },
      include: [{ association: 'Client' }, { association: 'Contractor' }],
    })

NOTE: Also tested with some other combinations, like only calling the class/model or with the following:

      include: [
        { model: Profile, as: 'Client' },
        { model: Profile, as: 'Contractor' },
      ],

Related code

The issue is occurring at the LEFT OUTER JOIN. Both compare using ON Contract.ContractorId

The expected query should be to compare CLIENT using ON Contract.ClientId and CONTRACTORusing ON Contract.ContractorId

NOTE: The other clauses like SELECT and WHERE were omitted since they are perfect

FROM `Contracts` AS `Contract`
    LEFT OUTER JOIN `Profiles` AS `Client` ON `Contract`.`ContractorId` = `Client`.`id`
    LEFT OUTER JOIN `Profiles` AS `Contractor` ON `Contract`.`ContractorId` = `Contractor`.`id`

Table: Contract

@Table({ timestamps: true })
export class Contract extends Model {
  ...

  @Column
  @ForeignKey(() => Profile)
  ContractorId!: string

  @BelongsTo(() => Profile)
  Contractor!: Profile

  @Column
  @ForeignKey(() => Profile)
  ClientId!: string

  @BelongsTo(() => Profile)
  Client!: Profile

  ...
}

Table: Profile

@Table({ timestamps: true })
export class Profile extends Model {
  @Column({ allowNull: false, type: DataType.STRING })
  name!: string

  ...

  @Column(DataType.ENUM('client', 'contractor'))
  type!: 'client' | 'contractor'

  @HasMany(() => Contract, 'ContractorId')
  Contractor!: Contract[]

  @HasMany(() => Contract, 'ClientId')
  Client!: Contract[]
}

[hpandelo]

Worked by adding the foreignKey right after the Model

  @Column
  @ForeignKey(() => Profile)
  ContractorId!: string

  @BelongsTo(() => Profile, 'ContractorId')
  Contractor!: Profile

  @Column
  @ForeignKey(() => Profile)
  ClientId!: string

  @BelongsTo(() => Profile, 'ClientId')
  Client!: Profile

Debugging I found that on base.js it's already using the wrong value, I just couldn't find where it was set that came from belongs-to.js constructor

class Association {
  constructor(source, target, options = {}) {
    this.source = source;
    this.target = target;
    this.options = options;
    this.scope = options.scope;
    this.isSelfAssociation = this.source === this.target;
    this.as = options.as;
    this.associationType = "";
    console.log(12, 'Association Class =>', this.source.name, options.as, options.foreignKey)
    if (source.hasAlias(options.as)) {
      throw new AssociationError(`You have used the alias ${options.as} in two separate associations. Aliased associations must have unique aliases.`);
    }
  }
  ....
 }

// Log Output: 12 Association Class => Contract Contractor { name: 'ContractorId' }
// Log Output: 12 Association Class => Contract Client { name: 'ContractorId' }

[hpandelo]

Update:

File: foreign-key-service.ts#L34

The break instruction will make the method return only the first foreign key from the methods In my scenario, the foreignKeys array retrieved from getForeignKeys(classWithForeignKey.prototype) it's like this:

[
  {
    relatedClassGetter: [Function (anonymous)],
    foreignKey: 'ContractorId'
  },
  {
    relatedClassGetter: [Function (anonymous)],
    foreignKey: 'ClientId'
  }
]

function getForeignKeyOptions(relatedClass, classWithForeignKey, foreignKey) {
    let foreignKeyOptions = {};
    ...
    if (!foreignKeyOptions.name && classWithForeignKey) {
        console.log(0, classWithForeignKey)
        const foreignKeys = getForeignKeys(classWithForeignKey.prototype) || [];

        for (let key of foreignKeys) {
            if (key.relatedClassGetter() === relatedClass ||
                relatedClass.prototype instanceof key.relatedClassGetter()) {
                foreignKeyOptions.name = key.foreignKey;
                break;
            }
        }
    }

    ...

    return foreignKeyOptions;
}
exports.getForeignKeyOptions = getForeignKeyOptions;