TypeORM relation through third table won't work

Question

Following the TypeORM tutorial (this part - https://typeorm.io/#/many-to-many-relations/many-to-many-relations-with-custom-properties) i created the models in my app:

@Entity({ name: 'organizations'})
export class OrganizationEntity extends AbstractEntity {
    @Column()
    name: string;

    @Column({ nullable: true })
    description: string;

    @Column({ nullable: true })
    logo: string;

    @ManyToOne(() => UserEntity, { nullable: true })
    mainContactUser: UserEntity;

    @OneToOne(() => LicenseEntity, license => license.organization)
    license: LicenseEntity;

    @OneToMany(() => UserRoleEntity, userRole => userRole.user)
    users!: UserRoleEntity[]
}

@Entity({ name: 'users' })
export class UserEntity extends AbstractEntityWithDates {
    @Column({ nullable: false })
    firstName: string;

    @Column({ nullable: false})
    lastName: string;

    @Column({ nullable: true })
    jobTitle: string;

    @Column({ unique: true })
    email: string;

    @Column({ nullable: true })
    phone: string;

    @Column({ nullable: true })
    avatar: string;

    @Column({ nullable: true })
    oAuthIdentityId: string;

    @OneToMany(() => UserRoleEntity, userRole => userRole.user)
    userRoles: UserRoleEntity[]
}

and finally:

@Entity({ name: 'user_roles' })
export class UserRoleEntity extends AbstractEntity {
    @ManyToOne(() => UserEntity, user => user.userRoles)
    user: UserEntity;

    @ManyToOne(() => OrganizationEntity, organization => organization.users, { nullable: true })
    organization: OrganizationEntity;

    @ManyToOne(() => ProjectEntity, { nullable: true })
    project: ProjectEntity;

    @Column({
        type: "enum",
        enum: RoleType,
        nullable: true
    })
    role: string;
}

Then in a service i'm performing a query where i want to fetch all organizations with at least related user roles:

        const found = await this.organizationRepository
            .createQueryBuilder("organization")
            .leftJoinAndSelect("organization.users", "users")
            .getMany();

No errors but the query generated by TypeORM is as follows:

SELECT "organization"."id" AS "organization_id", "organization"."name" AS "organization_name", "organization"."description" AS "organization_description", "organization"."logo" AS "organization_logo", "organization"."main_contact_user_id" AS "organization_main_contact_user_id", "users"."id" AS "users_id", "users"."role" AS "users_role", "users"."user_id" AS "users_user_id", "users"."organization_id" AS "users_organization_id", "users"."project_id" AS "users_project_id" 
FROM "organizations" "organization" 
LEFT JOIN "user_roles" "users" ON "users"."user_id"="organization"."id"

This part:

...ON "users"."user_id"="organization"."id"

is obviously incorrect but i can't see why?

Any help would be appreciated.

Answer 1

Your OrganizationEntity has an error in the OneToMany config; You set userRole.user, but it should be userRole.organization, which is the field in that table that links to this field.

    @OneToMany(() => UserRoleEntity, userRole => userRole.organization)
    users!: UserRoleEntity[]

Also, you should add primary keys to your tables. A safe approach by default is to use a Generated column. You can add it to your 3 tables. For the many-to-many table you could create a compound key, or simply an index with user_id, and company_id, but with tradeoffs - so just use an id on every table to get started.

    @PrimaryGeneratedColumn()
    id: string;