typeorm: Unknown column 'distinctAlias' when using `findOneOrFail` with relations
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"
}
About this issue
- Original URL
- State: closed
- Created 8 years ago
- Comments: 17
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
but this would work
also this would work in our case
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 skipidfield if you applyselect:["email", "comment"]which causes query to fail with ‘distinctAlias’ error. Is it by design or there is a bug?Same here. Manually add “id” to select seems to be a workaround.
Same here. In my case the problem occurred after adding {eager: true} to one of my entity’s relations
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
synchronizeproperty 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.
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
distinctAliasis 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
I’ve came cross the same problem
@pleerock Did u think about it, to repair?
I have the same issue if i use eager: true on my relation
i must manually add “id” for select
but if i remove eager, then everything work fine, that’s problem with eager is incompatibile with select wtihout declared “id”.
Same here, but I dont have relations. Instead, I have
selectin the find options.