Duplicate column name (ER_DUP_FIELDNAME errno: 1060) when join table

[valdeua]

Bug Report

Current behavior

When I try to join ('eager' type) related entity I received 'Duplicate column name' [Nest] 39063 - 11/30/2022, 12:11:42 PM ERROR [ExceptionsHandler] Duplicate column name 'Fundraising_id'

Here is an exception:

I think an important precondition here is: I am trying to get data with ?limit When I tried to get data without limit I didn't have error:

Expected behavior

Eagerly joined column without duplication error

What I have tried

I tried aliases but it doesn't help https://github.com/nestjsx/crud/issues/281#issuecomment-566964787 relation field renaming also wasn't helpful

My CRUD example:

@Crud({
  model: {
    type: Fundraising,
  },
  query: {
    join: {
      fundraiser: {
        eager: false,
        alias: 'fr',
      },
      cards: {
        eager: false,
        alias: 'c',
      },
    },
  },

Models:

export abstract class BaseEntity {
    @PrimaryGeneratedColumn()
    id: number;

    @CreateDateColumn()
    createdAt: Date;

    @UpdateDateColumn()
    updatedAt: Date;

    @DeleteDateColumn()
    deletedAt: Date;
}

@Entity()
export class Fundraising extends BaseEntity {
    @Column()
    @Index({ unique: false })
    name: string;

    @ManyToOne(() => Fundraiser, (f) => f.fundraisingList, {
        nullable: true,
        eager: true,
    })
    @JoinColumn()
    fundraiser: Fundraiser;
}

@Entity()
export class Fundraiser extends BaseEntity {
    @Column()
    @Index({ unique: false })
    name: string;

    @Column()
    photo: string;

    @OneToMany(() => Fundraising, (fr) => fr.fundraiser, {
        nullable: true,
    })
    fundraisingList: Fundraising[];
}

Generated Query example

SELECT DISTINCT `distinctAlias`.`Fundraising_id` AS `ids_Fundraising_id`, `distinctAlias`.`Fundraising_id`
FROM (SELECT `Fundraising`.`id`                 AS `Fundraising_id`,
             `Fundraising`.`id`                 AS `Fundraising_id`,
             `Fundraising`.`created_at`         AS `Fundraising_created_at`,
             `Fundraising`.`updated_at`         AS `Fundraising_updated_at`,
             `Fundraising`.`deleted_at`         AS `Fundraising_deleted_at`,
             `Fundraising`.`is_published`       AS `Fundraising_is_published`,
             `Fundraising`.`name`               AS `Fundraising_name`,
             `Fundraising`.`description`        AS `Fundraising_description`,
             `Fundraising`.`target`             AS `Fundraising_target`,
             `Fundraising`.`mono_jar_url`       AS `Fundraising_mono_jar_url`,
             `Fundraising`.`raised_mono`        AS `Fundraising_raised_mono`,
             `Fundraising`.`raised_externally`  AS `Fundraising_raised_externally`,
             `Fundraising`.`image`              AS `Fundraising_image`,
             `Fundraising`.`is_ended`           AS `Fundraising_is_ended`,
             `Fundraising`.`report_video_link`  AS `Fundraising_report_video_link`,
             `Fundraising`.`report_photo`       AS `Fundraising_report_photo`,
             `Fundraising`.`report_description` AS `Fundraising_report_description`,
             `fr`.`id`                          AS `fr_id`,
             `fr`.`id`                          AS `fr_id`,
             `fr`.`created_at`                  AS `fr_created_at`,
             `fr`.`updated_at`                  AS `fr_updated_at`,
             `fr`.`deleted_at`                  AS `fr_deleted_at`,
             `fr`.`name`                        AS `fr_name`,
             `fr`.`description`                 AS `fr_description`,
             `fr`.`photo`                       AS `fr_photo`,
             `Fundraising`.`fundraiser_identifier`
      FROM `fundraising` `Fundraising`
               LEFT JOIN `fundraiser` `fr`
                         ON `fr`.`id` = `Fundraising`.`fundraiser_identifier` AND (`fr`.`deleted_at` IS NULL)
      WHERE `Fundraising`.`deleted_at` IS NULL) `distinctAlias`
ORDER BY `distinctAlias`.`Fundraising_id` DESC, `Fundraising_id` ASC LIMIT 100

For Tooling issues:

• Node version: 16.18
• Platform: Mac
• Database: MySQL

Packages:

{
 "@nestjs/core": "^9.0.0",
 "@nestjsx/crud": "^5.0.0-alpha.3",
 "@nestjsx/crud-typeorm": "^5.0.0-alpha.3",
 "@nestjs/typeorm": "^9.0.0",
  "typeorm": "^0.3.7"
}

[mnedok]

Take a look at this project: https://github.com/gid-oss/dataui-nestjs-crud

[danielsharvey]

@valdeua I think this fix is wha you want - https://github.com/rewiko/crud/pull/20

[IB21-A]

I'm having the same issue. Is there any plan to update this library? Or should be switching to the forked projects?

[andkom]

I have same issue when use limit in query:

@Crud({
  model: {
    type: TransactionEntity,
  },
  routes: {
    only: ['getManyBase', 'getOneBase'],
  },
  query: {
    alwaysPaginate: true,
    limit: 25,
    join: {
      deposit: {
        eager: true,
      },
  },
})

// QueryFailedError: ER_DUP_FIELDNAME: Duplicate column name 'TransactionEntity_id'

[ameen-elhoseiny]

same issue here , anyone has a solution ?