Problem with One-To-Many and Many-To-One relations in TypeORM: Incorrect SQL query generated

Question

I'm learning typeORM and I'm stucked with a problem. I've created 3 entities, CategoryTypeAttribute, ItemAttribute and CategoryType.

CategoryTypeAttribute entity:

@Entity('category_type_attribute')
export class CategoryTypeAttribute {
    @PrimaryColumn('uuid', {
        unique: true,
        nullable: false,
        comment: 'Unique identifier for the category type attribute',
    })
    @Generated('uuid')
    id: string;

    @PrimaryColumn({
        name: 'category_type_id',
        comment: 'Id of the associated category type',
        length: 36,
    })
    categoryTypeId: string;

    @ManyToOne(() => CategoryType, (categoryType) => categoryType.categoryTypeAttributes)
    @JoinColumn({ name: 'category_type_id' })
    categoryType: CategoryType;

    @OneToMany(() => ItemAttribute, (itemAttribute) => itemAttribute.categoryTypeAttribute)
    itemAttributes: ItemAttribute[]

    @Column({ type: 'varchar', length: 64, comment: 'Name of the attribute' })
    name: string;

    @Column({
        name: 'data_type',
        type: 'enum',
        enum: ItemDataTypes,
        comment: 'Data type of the attribute',
    })
    dataType: ItemDataTypes;

    @Column({
        name: 'is_required',
        type: 'boolean',
        comment: 'Flag indicating if the attribute is required',
    })
    isRequired: boolean;

    @Column({
        name: 'is_enabled',
        type: 'boolean',
        default: true,
        comment: 'Flag indicating if the attribute is enabled (active)',
    })
    isEnabled: boolean;

    @Column('timestamp', {
        name: 'update_date',
        default: () => 'CURRENT_TIMESTAMP',
        comment: 'Update date of the attribute',
    })
    updateDate: Date;
}

ItemAttribute entity:

@Entity('item_attribute')
export class ItemAttribute {
    @PrimaryColumn('uuid', {
        name: 'item_id',
        length: 36,
        comment: 'Id of the item to which the attribute belongs',
    })
    itemId: string;

    @ManyToOne(() => Item)
    @JoinColumn({ name: 'item_id' })
    item: Item;

    @PrimaryColumn('uuid', {
        name: 'category_type_attribute_id',
        length: 36,
        comment: 'Id of the associated category type attribute',
    })
    categoryTypeAttributeId: string;

    @ManyToOne(() => CategoryTypeAttribute, (categoryTypeAttribute) => categoryTypeAttribute.itemAttributes)
    @JoinColumn({ name: 'category_type_attribute_id' })
    categoryTypeAttribute: CategoryTypeAttribute;

    @ManyToOne(() => User, (user) => user.id)
    @JoinColumn({ name: 'update_user_id' })
    updateUser: User;

    @Column({
        type: 'varchar',
        length: 128,
        nullable: true,
        comment: 'Value of the attribute',
    })
    value: string | null;

    @Column('timestamp', {
        name: 'update_date',
        default: () => 'CURRENT_TIMESTAMP',
        comment: 'Update date of the attribute',
    })
    updateDate: Date;
}

and CategoryType entity:

@Entity('category_type')
export class CategoryType {
    @PrimaryColumn('uuid', {
        unique: true,
        nullable: false,
        comment: 'Unique identifier for the category type',
    })
    @Generated('uuid')
    id: string;

    @Column({
        length: 128,
        nullable: false,
        comment: 'Name for the category type',
    })
    name: string;

    @Column({
        length: 4,
        nullable: false,
        comment: 'Code for the category type',
    })
    code: string;

    @Column('text', {
        nullable: true,
        comment: 'Description for the category type',
    })
    description: string;

    @Column('timestamp', {
        name: 'create_date',
        default: () => 'CURRENT_TIMESTAMP',
        comment: 'The creation date of the category type',
    })
    createDate: Date;

    @OneToMany(() => Category, (category) => category.categoryType)
    category: Category[];

    @OneToMany(
        () => CategoryTypeAttribute,
        (categoryTypeAttribute) => categoryTypeAttribute.categoryType
    )
    categoryTypeAttributes: CategoryTypeAttribute[];
}

The problem is I'm trying to join CategoryTypeAttribute and ItemAttribute to obtain a result with category type attribute id, category type attribute name, and item attribute value.

I've read documentation and blog examples and based on that I created the following query builder:

    const categoryTypeAttributes = await dataSource
        .getRepository(CategoryTypeAttribute)
        .createQueryBuilder('category_type_attribute')
        .select([
            'category_type_attribute.id',
            'category_type_attribute.name',
            'item_attribute.value',
        ])
        .leftJoin(
            'category_type_attribute.itemAttributes',
            'item_attribute'
        )
        .getMany();

The result obtained is not the desired, I've checked the sql query generated by the ORM and is this:

SELECT
    `category_type_attribute`.`id` AS `category_type_attribute_id`,
    `category_type_attribute`.`name` AS `category_type_attribute_name`,
    `category_type_attribute`.`category_type_id` AS `category_type_attribute_category_type_id`,
    `item_attribute`.`value` AS `item_attribute_value`,
    `item_attribute`.`item_id` AS `item_attribute_item_id`,
    `item_attribute`.`category_type_attribute_id` AS `item_attribute_category_type_attribute_id`
FROM
    `category_type_attribute` `category_type_attribute`
    LEFT JOIN `item_attribute` `item_attribute`
    ON `item_attribute`.`category_type_attribute_id` = `category_type_attribute`.`category_type_id`
    AND `item_attribute`.`category_type_attribute_id` = `category_type_attribute`.`category_type_id`;

I'm confused because to join both tables it's using item_attribute.category_type_attribute_id, which is correct, and category_type_attribute.category_type_id, which is incorrect, it should be id.

So the sql query I was waiting was the following:

SELECT
    `category_type_attribute`.`id` AS `category_type_attribute_id`,
    `category_type_attribute`.`name` AS `category_type_attribute_name`,
    `category_type_attribute`.`category_type_id` AS `category_type_attribute_category_type_id`,
    `item_attribute`.`value` AS `item_attribute_value`,
    `item_attribute`.`item_id` AS `item_attribute_item_id`,
    `item_attribute`.`category_type_attribute_id` AS `item_attribute_category_type_attribute_id`
FROM
    `category_type_attribute` `category_type_attribute`
    LEFT JOIN `item_attribute` `item_attribute`
    ON `item_attribute`.`category_type_attribute_id` = `category_type_attribute`.`id`;

This query is the one I need.

I don't know where I'm having a problem, if the relations between entities are wrong or the query builder is wrong. I've tried to start the query from item_attribute entity and the result is the same.

Answer 1

I think you need to specify your join condition explicitly like this:

const categoryTypeAttributes = await dataSource
    .getRepository(CategoryTypeAttribute)
    .createQueryBuilder('category_type_attribute')
    .select([
        'category_type_attribute.id',
        'category_type_attribute.name',
        'item_attribute.value',
    ])
    .leftJoin('category_type_attribute.itemAttributes', 'item_attribute', 'item_attribute.categoryTypeAttributeId = category_type_attribute.id')
    .getMany();