Crud: SQL exception for nested resources

[nikitammf]

I have relations between resources

@Entity('boards')
export class BoardEntity extends ExtendedEntity {
    @PrimaryGeneratedColumn()
    public id: string;

    @Column()
    public name: string;

    @OneToMany(() => ListEntity, ({ board }) => board, { eager: true, onDelete: 'CASCADE' })
    @JoinColumn()
    public lists: ListEntity[];
}

```js
@Entity('lists')
export class ListEntity extends ExtendedEntity {
@PrimaryGeneratedColumn()
public id: string;

@Column()
public name: string;

@Column()
public order: number;

@ManyToOne(() => BoardEntity, ({ lists }) => lists)
public board: BoardEntity;

}

and I try to organize nested resources. I used the following controller to do it
```js
@Crud({
    model: {
        type: ListEntity,
    },
    params: {
        boardId: {
// ----------------------------------------------------------
            field: 'board.id',
// ----------------------------------------------------------
            type: 'number',
        },
    },
    query: {
        sort: [
            {
                field: 'order',
                order: 'ASC',
            },
        ],
    },
})
@Controller('boards/:boardId/lists')
export class ListController {
    constructor(public readonly service: ListService) {}
}

I set to params "boardId" value of field "board.id".
As result api endpoint returns error(500).

[nikitammf]

I was researching and I have found the source of this exception.

SQL query is formed incorrectly:

SELECT 
    "ListEntity"."version" AS "ListEntity_version", 
    "ListEntity"."cratedAt" AS "ListEntity_cratedAt",
    "ListEntity"."updatedAt" AS "ListEntity_updatedAt",
    "ListEntity"."id" AS "ListEntity_id", 
    "ListEntity"."name" AS "ListEntity_name", 
    "ListEntity"."order" AS "ListEntity_order", 
    "ListEntity"."boardId" 
FROM 
    "lists" "ListEntity" 
WHERE 
    board.id = 2 
ORDER BY 
    "ListEntity"."order" ASC 

Correct SQL query should replace _"board.id"_ in where section to _"ListEntity"."boardId"_

I have found the method "mapOperatorsToQuery" in "typeorm-crud.service.ts" which has the condition:

const field = cond.field.indexOf('.') === -1 ? `${this.alias}.${cond.field}` : cond.field;

If this condition replace to

const field = `${this.alias}.${cond.field}`;

sql query is formed correctly.

[Diluka]

@Entity('lists')
export class ListEntity extends ExtendedEntity {
    @PrimaryGeneratedColumn()
    public id: string;

    @Column()
    public name: string;

    @Column()
    public order: number;

    // Explicitly define foreign keys
    @Column()
    public boardId: number;

    @ManyToOne(() => BoardEntity, ({ lists }) => lists)
    public board: BoardEntity;
}

@nikitammf define the foreign keys explicitly, so you can use it directly in your code.

And unfortunately your PR #175 is conflict with #146 which to solve #87 and #105

see https://typeorm.io/#/relations-faq/how-to-use-relation-id-without-joining-relation

[nikitammf]

Thank you, @Diluka ! When the foreign key has been defined SQL exception disappears.