Crud: ER_DUP_FIELDNAME: Duplicate column name

[mreschke]

It is very common for databases to stick with underscore style column names, especially for relationships like user_id, category_id, space_id...

By default TypeORM names all relation columns in camelCase spaceId, userId.... You can override this behavior in TypeORM like so

@ManyToOne(type => Space, space => space.posts, { nullable: false })
@JoinColumn({ name: "space_id" }) // NOTICE I am telling TypeORM to use space_id
space: Space;

Now the database column is space_id instead of spaceId...good. All works well across the board until CRUD does a JOIN. I have only tested on a OneToMany. My basic setup is a POSTS entity that can have ONE space. So a basic OneToMany/ManyToOne relation is setup.

When your JOIN query runs it actually runs 2 queries. The first query errors with ER_DUP_FIELDNAME: Duplicate column name space_id because you assume you can call one of the columns as 'space_id' yourself, but its already taken. Here is your first JOIN query that errors

SELECT
    DISTINCT `distinctAlias`.`Post_id` as ids_Post_id
FROM (
    SELECT
        `Post`.`id` AS `Post_id`,
        `Post`.`slug` AS `Post_slug`,
        `Post`.`title` AS `Post_title`,
        `Post`.`body` AS `Post_body`,
        `Post`.`hidden` AS `Post_hidden`,
        `Post`.`deleted` AS `Post_deleted`,
        `Post`.`indexed_at` AS `Post_indexed_at`,
        `Post`.`created_at` AS `Post_created_at`,
        `Post`.`updated_at` AS `Post_updated_at`,
------------------
PROBLEM IS HERE
        `space`.`id` AS `space_id`,
        `space`.`name` AS `space_name`,
        `space`.`section` AS `space_section`,
        `space`.`slug` AS `space_slug`,
        `space`.`order` AS `space_order`,
------------------
        `Post`.`parent_id`,
        `Post`.`space_id`,
        `Post`.`format_id`,
        `Post`.`created_by`,
        `Post`.`updated_by`
    FROM
        `posts` `Post`
        INNER JOIN `spaces` `space` ON `space`.`id`=`Post`.`space_id`
) `distinctAlias`
ORDER BY `Post_id` ASC LIMIT 25

Notice you are adding all your AS statements to the space table and assuming you can just append the column name after an _ as space_id, as space_name etc...well space_id is already used. If you were to change your AS statement to something that couldn't possibly clash like space_table_id for example... or double understoce space__id, space__name etc...

Because of this I cannot set any of my ids to use underscores now.

[mreschke]

This is also related to #280. Because if you fix #280 and eliminate that first query (which I think is pointless) you end up with only one actual JOIN query, and believe it or not that query does NOT complain if there are duplicate columns. The second query that runs is this one

SELECT
    `Post`.`id` AS `Post_id`,
    `Post`.`slug` AS `Post_slug`,
    `Post`.`title` AS `Post_title`,
    `Post`.`body` AS `Post_body`,
    `Post`.`hidden` AS `Post_hidden`,
    `Post`.`deleted` AS `Post_deleted`,
    `Post`.`indexed_at` AS `Post_indexed_at`,
    `Post`.`created_at` AS `Post_created_at`,
    `Post`.`updated_at` AS `Post_updated_at`, 
    `space`.`id` AS `space_id`,
    `space`.`name` AS `space_name`,
    `space`.`section` AS `space_section`,
    `space`.`slug` AS `space_slug`,
    `space`.`order` AS `space_order`,
    `Post`.`parent_id`,
    `Post`.`space_id`,
    `Post`.`format_id`,
    `Post`.`created_by`,
    `Post`.`updated_by`
FROM
    `posts` `Post`
    INNER JOIN `spaces` `space` ON `space`.`id`=`Post`.`space_id`

Notice there are still 2 space_id columns in the result, by MySQL does not error. Only in your first query that basically get a list of IDs only with a sub select query (distinctAlias) is the one that errors with duplicate issue.

[mreschke]

I have some new information on this. If I comment out the maxLimit: 25, line in my controller the query changes to just a single proper JOIN without running 2 queries, the first of which crashes with the duplicate space_id column error. Just like in #280, not sure why adding maxLimit would later one JOIN into 2 queries with an IN statement. Either way, the first query still crashes with duplicate column.

[zMotivat0r]

@mreschke in the version 4.3.0-beta the alias option for a relation was introduced. Please give it a shot and let me know if you still have an issue with this

[githubmann]

how to solve the problem

[zMotivat0r]

@githubmann as I mentioned in my last comment - use alias

[githubmann]

can you give some example code, thank you

[githubmann]

@zMotivat0r

i try, is ok. but no typo.

[zMotivat0r]

@mkelandis @githubmann is it still an issue when using with alias?

[githubmann]

@zMotivat0r that's no problem. just no type, must convert to 'any'.
i appreciate your effort.

[kop7]

• "@nestjs/common": "^6.0.0"
• "@nestjsx/crud": "^4.4.0",
• "@nestjsx/crud-request": "^4.4.0",
• "@nestjsx/crud-typeorm": "^4.4.0",
• "typeorm": "^0.2.18"

the problem is still here?

@Entity('testA')
export class TestA {
    @PrimaryGeneratedColumn()
    id: number;

    @OneToOne(() => TestB, testb => testb.id, { cascade: true })
    @JoinColumn()
    test: TestB;
}

@Entity('testB')
export class TestB {
    @PrimaryGeneratedColumn()
    id: number;

    @OneToMany(() => TestC, testc => testc.cat, { cascade: true })
    testc: TestC[];

    @OneToMany(() => TestD, testd => testd.cat, { cascade: true })
    testd: TestD[];
}

@Crud({
    model: { type: TestA },
    query: {
        join:{
            'test': { eager: true},
            'test.testc': {alias: 'ttestc', eager: true} as any,
            'test.testd': {alias: 'ttestd',  eager: true} as any,
        }
    }
})

GET api/tests/1

error: "message": "ER_DUP_FIELDNAME: Duplicate column name 'catId'",

@zMotivat0r

[zMotivat0r]

@kop7 set an alias for test relation too

[satuuntuksemuaaja]

alias not work for me