Database Error: eager loaded column of the junction table within a M:N relationship doesn't exist

[razvan286]

I've been spending a couple of days trying to find the solution for this but no luck still. The following issue doesn't happen on localhost, it only happens in a Docker container (I always use the build --no-cache command).

I have three models in place Activity, Item, and Label.
Self-referential One-to-Many relationship in Label with an alias as subLabels.
M:N relationship between Activity and Label
M:N relationship between Item and Label
I also have an explicit junction model named LabelAssociations.

The Activity model:

@Table({ tableName: 'activities' })
export default class Activity extends Model {
  @PrimaryKey
  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  declare id: string;

  @AllowNull(false)
  @Column(DataType.STRING)
  declare name: string;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;

  @BelongsToMany(() => Label, {
    foreignKey: 'activityId',
    otherKey: 'labelId',
    through: { model: 'LabelAssociations', unique: false },
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare labels: ReturnType<() => Label[]>;

  // Declare 'super many-to-many' association to be able to query all associated subLabels
  @HasMany(() => LabelAssociations, {
    foreignKey: 'activityId',
    as: 'associatedSubLabels',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare associatedSubLabels: ReturnType<() => LabelAssociations[]>;

Need to mention that I explicitly specify the @HasMany LabelAssociations relationship to be able to query the junction table by the activityId as you can see below.

The Item model:

@Table({ tableName: 'items' })
export default class Item extends Model {
@PrimaryKey
@IsUUID(4)
@Default(DataType.UUIDV4)
@Column(DataType.UUID)
declare id: string;

@Column(DataType.STRING)
declare questionPhrase: string;

@UpdatedAt
declare updatedAt: Date;

@CreatedAt
declare createdAt: Date;

// Foreign keys

@BelongsToMany(() => Label, {
  foreignKey: 'itemId',
  otherKey: 'labelId',
  through: { model: 'LabelAssociations', unique: false },
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
})
declare labels: ReturnType<() => Label[]>;

// Declare 'super many-to-many' association to be able to query all associated subLabels
@HasMany(() => LabelAssociations, {
  foreignKey: 'itemId',
  as: 'associatedSubLabels',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
})
declare associatedSubLabels: ReturnType<() => LabelAssociations[]>;

The Label model:

@Table({ tableName: 'labels' })
export default class Label extends Model {
@PrimaryKey
@IsUUID(4)
@Default(DataType.UUIDV4)
@Column(DataType.UUID)
declare id: string;

@AllowNull(false)
@Column(DataType.STRING)
declare name: string;

@AllowNull(true)
@Column(DataType.STRING)
declare color: string;

@UpdatedAt
declare updatedAt: Date;

@CreatedAt
declare createdAt: Date;

// Foreign keys
@AllowNull(true)
@ForeignKey(() => UserProfile)
@Column(DataType.INTEGER)
declare ownerId: number;

@AllowNull(true)
@ForeignKey(() => Label)
@Column(DataType.UUID)
declare labelId: string;

// Associations
@BelongsTo(() => UserProfile, {
  as: 'owner',
})
declare owner: ReturnType<() => UserProfile>;

@HasMany(() => Label, {
  foreignKey: 'labelId',
  as: 'subLabels',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
})
declare subLabels: ReturnType<() => Label[]>;

The LabelAssociations model:

@Table({ tableName: 'labelAssociations' })
export default class LabelAssociations extends Model {
  @PrimaryKey
  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  declare id: string;

  @AllowNull(true)
  @ForeignKey(() => Activity)
  @Unique(false)
  @Default(null)
  @Column(DataType.UUID)
  declare activityId: string;

  @AllowNull(true)
  @ForeignKey(() => Item)
  @Unique(false)
  @Default(null)
  @Column(DataType.UUID)
  declare itemId: string;

  @AllowNull(false)
  @ForeignKey(() => Label)
  @Unique(false)
  @Column(DataType.UUID)
  declare labelId: string;

  @AllowNull(true)
  @ForeignKey(() => Label)
  @Unique(false)
  @Default(null)
  @Column(DataType.UUID)
  declare subLabelId: string;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;
}

I have explicitly created a model for the junction table to be able to create the foreign keys: activityId, and itemId. I have other M:N relationships in my application where it didn't require to explicitly create the junction model and everything works properly there.

I will exemplify the errors by only referring to the Activity model as the Item queries are mostly similar.

After a lot of trials I got to the following query by having to explicitly specify the attributes that I want to be loaded for the Label eager loadings, otherwise other attributes would've been loaded:

Activity.findByPk(id, {
    include: [
      {
        model: Label,
        as: 'labels',
        where: { labelId: null },
        required: false, // Set required to false to include an empty array even if no labels are found
        through: { attributes: [] }, // Exclude eager loading the junction table
        attributes: [
          'id',
          'name',
          'ownerId',
          'color',
          'labelId',
          'createdAt',
          'updatedAt',
        ],
        include: [
          {
            model: Label,
            as: 'subLabels',
            where: { [Op.not]: { labelId: null } },
            attributes: [
              'id',
              'name',
              'ownerId',
              'color',
              'labelId',
              'createdAt',
              'updatedAt',
            ],
            required: false,
          },
        ],
      },
      {
        model: LabelAssociations,
        as: 'associatedSubLabels',
        attributes: ['labelId', 'subLabelId', 'activityId'],
        where: { activityId: id },
        required: false, // Always eager load
      },
    ],
  });

With the current model definitions and the just mentioned query, when this query is performed in the Docker container I get the following error:
ERROR: column labels->LabelAssociations.activityId does not exist

The complete query:

SELECT
    "D"."id",
    "D"."name",
    "D"."createdAt",
    "D"."updatedAt",
    "labels"."id" AS "labels.id",
    "labels"."name" AS "labels.name",
    "labels"."ownerId" AS "labels.ownerId",
    "labels"."color" AS "labels.color",
    "labels"."labelId" AS "labels.labelId",
    "labels"."createdAt" AS "labels.createdAt",
    "labels"."updatedAt" AS "labels.updatedAt",
    "labels->subLabels"."id" AS "labels.subLabels.id",
    "labels->subLabels"."name" AS "labels.subLabels.name",
    "labels->subLabels"."ownerId" AS "labels.subLabels.ownerId",
    "labels->subLabels"."color" AS "labels.subLabels.color",
    "labels->subLabels"."labelId" AS "labels.subLabels.labelId",
    "labels->subLabels"."createdAt" AS "labels.subLabels.createdAt",
    "labels->subLabels"."updatedAt" AS "labels.subLabels.updatedAt",
    "associatedSubLabels"."id" AS "associatedSubLabels.id",
    "associatedSubLabels"."labelId" AS "associatedSubLabels.labelId",
    "associatedSubLabels"."subLabelId" AS "associatedSubLabels.subLabelId",
    "associatedSubLabels"."activityId" AS "associatedSubLabels.activityId"
FROM
    "activities" AS "D"
LEFT OUTER JOIN (
    "LabelAssociations" AS "labels->LabelAssociations"
    INNER JOIN "labels" AS "labels" ON "labels"."id" = "labels->LabelAssociations"."labelId"
) ON "D"."id" = "labels->LabelAssociations"."activityId" AND "labels"."labelId" IS NULL
LEFT OUTER JOIN "labels" AS "labels->subLabels" ON "labels"."id" = "labels->subLabels"."labelId" AND NOT ("labels->subLabels"."labelId" IS NULL)
LEFT OUTER JOIN "labelAssociations" AS "associatedSubLabels" ON "D"."id" = "associatedSubLabels"."activityId" AND "labelAssociations"."activityId" = 'ffffffff-aaaa-aaaa-aaaa-aaaaaaaaaaa3' WHERE "D"."id" = 'ffffffff-aaaa-aaaa-aaaa-aaaaaaaaaaa3'

I need to mentioned that I can get rid of the error from above by updating the relationship specified in the Activity model from:

  @BelongsToMany(() => Label, {
    foreignKey: 'activityId',
    otherKey: 'labelId',
    through: { model: 'LabelAssociations', unique: false },
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare labels: ReturnType<() => Label[]>;

to:

  @BelongsToMany(() => Label, {
  foreignKey: 'activityId',
  otherKey: 'labelId',
  through: 'labelAssociations',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
})
declare labels: ReturnType<() => Label[]>;

But with this change I then run into other issues where queries on the junction table fail such as:

    await LabelAssociations.create(
    {
      activityId,
      labelId,
    },
    { returning: false },
  );

The above query was failing because instead of activityId was trying to add to the itemId column a null value as it wasn't specified. It seemed that it was only able to verify the itemId foreign key and the activityId was somehow missing.

I have previously seen some other opened issues similar to mine. However, some of them were happening because of the LIMIT specified in the query which is not the case here.

Any help would be appreciated. Thank you in advance.

Dialect: postgres
Database version: 15
Sequelize version: 6.25.3
Sequelize-typescript version: 2.1.6

EDIT:

I think I was assessing the error from a bad point of view. The actual error is that sequelize doesn't recognize the activityId foreign key column in the LabelAssociations model.