Unknown column 'distinctAlias' when using `findOneOrFail` with relations

[alusev]

Issue type:

[ ] question [x] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [x] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[] latest [ ] @next [x] 0.2.17 (or put your version here)

Steps to reproduce or a small repository showing the problem:

Model

@Entity({
    name: 'clients'
})
export class Client {

    @PrimaryColumn({ type: 'int', width: 11, select: false, name: 'user_id' })
    id: number;

    @Column({ type: 'int', width: 11, select: false })
    client_group_id: number;

    @Column({ length: 500, default: '' })
    email: string;

    @Column({ type: 'text' })
    comment: string;


    //
    // Relationships 

    @OneToOne(type => User, user => user.client)
    @JoinColumn({ name: 'user_id' })
    user: User;
}

@Entity({ name: 'users' })
@Index(["username", "deleted"])
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: 'datetime', nullable: true, default: null, readonly: true })
    created?: Date;

    @Column({ type: 'datetime', nullable: true, default: null })
    updated?: Date;

    @Column({ type: 'datetime', nullable: true, default: null, select: false })
    deleted?: Date | null;

    @Column({ length: 500 })
    username: string;

    @Column({ length: 200, select: false })
    password?: string;

    @OneToOne(type => Client, client => client.user, { nullable: true })
    client: Client | null;
}

Query:

const clientRepository = getRepository(Client);
    const client = await clientRepository.findOneOrFail(clientId, {
        relations: ["user"]
    });

Error

{
"message":
   "ER_BAD_FIELD_ERROR: Unknown column 'distinctAlias.Client_user_id' in 'field list'",
 "code": "ER_BAD_FIELD_ERROR",
 "errno": 1054,
 "sqlMessage":
   "Unknown column 'distinctAlias.Client_user_id' in 'field list'",
 "sqlState": "42S22"
}

[Melzar]

We have a similar issue and the which in our case. The issue has been introduced ( from our code base point of view ) in version 0.2.10 in v0.2.9 everything works fine. In v0.2.9 findOneOrFail method adds automatically 'id' field to query where in later versions id field is missing. Also SELECT DISTINCT is performed

So this code wouldn't work in our case

    const client = await clientRepository.findOneOrFail(clientId, {
        relations: ["user"]
        select:["email", "comment"]
    });

but this would work

    const client = await clientRepository.findOneOrFail(clientId, {
        relations: ["user"]
        select:["id", "email", "comment"]
    });

also this would work in our case

    const client = await clientRepository.findOneOrFail(clientId, {
        relations: ["user"]
    });

as it adds all of the column so I'm not sure why it's not working in your case. The question is, why in v.0.2.9 findOneOrFail runs normal SELECT statement and adds automatically ID even if you filter it by applying select:["email", "comment"] as parameter and from v.0.2.10 it runs SELECT DISTINCT statement and skip id field if you apply select:["email", "comment"] which causes query to fail with 'distinctAlias' error. Is it by design or there is a bug?

[reuwi]

I've came cross the same problem

[alvis]

Same here, but I dont have relations. Instead, I have select in the find options.

[thezzisu]

Same here. Manually add "id" to select seems to be a workaround.

[zecka]

Same here. In my case the problem occurred after adding {eager: true} to one of my entity's relations

[Yehonal]

Probably related: https://github.com/typeorm/typeorm/issues/4998

[ZungTa]

same. I'm using postgresql.

[BaharaJr]

same. I'm using postgresql.

Hello @ZungTa I found the solution was to use explicitly use where i.e

    const client = await clientRepository.findOneOrFail({
       where: {clientId}
        relations: ["user"]
        select:["email", "comment"]
    });

[ZungTa]

Thank you for answer. @BaharaJr I think maybe this is my DB Entity code problem. I am using two DBs, the first DB has the synchronize property false. It may be a problem because the entity code does not exactly match the contents of the actual DB table. I solved it by adding 'id' to the select array.

like this.

const client = await clientRepository.findOneOrFail({
  where: {clientId},
  select:["id", "email", "comment"]
});

Also, the error that appears when there is no 'id' in the select array is as follows. 'QueryFailedError: column distinctAlias.AccountsUser_id does not exist\n'

I looked up the typeorm code to see why distinctAlias is displayed. I think it's probably related to the part below.

https://github.com/typeorm/typeorm/blob/0659ec395298390a2ec3e39ecae1ab4764c4e41a/src/query-builder/SelectQueryBuilder.ts#L2735-L2770

[PatrykKuniczak]

@pleerock Did u think about it, to repair?

I have the same issue if i use eager: true on my relation

 @OneToOne(() => UserAvatar, userAvatar => userAvatar.user, {
        onDelete: "SET NULL",
        eager: true
    })
    @JoinColumn()
    userAvatar?: UserAvatar;

i must manually add "id" for select

 .findOneOrFail({
                where: { username },
                select: ["id", "password"]
            })

but if i remove eager, then everything work fine, that's problem with eager is incompatibile with select wtihout declared "id".