'Can't get the values from the joined table even using .innerJoinAndSelect
I have these simple entities with an OneToMany and relative ManyToOne relation/foreign key, and exploring the tables generated in mysql it's all in place. Doing an inner join I need data from both tables, but what is returned is only from the first. Note that I'm using both .innerJoinAndSelect() and .addSelect(). Passing the query generated with .getSql() to Workbench, it returns correctly the desired values, so code and relations must be ok
What is returned instead (lacks the 'createdAt' field from the joined table)
[
{
"idRole": 1,
"role": "Global Admin"
}
]
Roles entity
@Entity({ name: 'roles' })
export class RoleEntity {
@PrimaryGeneratedColumn({ unsigned: true })
idRole: number;
@Column({ nullable: false })
role: string;
@Column({ default: 1, unsigned: true })
priority: number;
@OneToMany(type => RolesUsersRelationsEntity, relation => relation.idRelation)
relation: RolesUsersRelationsEntity[];
}
Relations between roles and users
@Entity({ name: 'roles_users_relations' })
export class RolesUsersRelationsEntity {
@PrimaryGeneratedColumn()
idRelation: number;
@Column({ nullable: false, unsigned: true })
idRole: number;
@Column({ nullable: false, unsigned: true })
idUser: number;
@CreateDateColumn()
createdAt: Date;
@ManyToOne(type => UserEntity, user => user.relation)
@JoinColumn({ name: 'idUser' })
user: UserEntity;
@ManyToOne(type => RoleEntity, role => role.relation)
@JoinColumn({ name: 'idRole' })
role: RoleEntity;
}
Service code
const assignedRoles = await this.repoRoles
.createQueryBuilder('roles')
.innerJoinAndSelect(
RolesUsersRelationsEntity,
'relations',
'relations.idRole = roles.idRole',
)
.select(['roles.idRole', 'roles.role', 'relations.createdAt'])
.addSelect('relations.createdAt')
.where('relations.idUser = :idUser', { idUser })
.orderBy('roles.priority', 'ASC')
.getMany();
The query generated with .getSql()
SELECT `roles`.`idRole` AS `roles_idRole`, `roles`.`role` AS `roles_role`, `relations`.`createdAt` AS `relations_createdAt`
FROM `roles` `roles`
INNER JOIN `roles_users_relations` `relations` ON `relations`.`idRole` = `roles`.`idRole`
WHERE `relations`.`idUser` = ? ORDER BY `roles`.`priority` ASC
What I'm doing wrong?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
