How to generate query to filter according to foreign Key in NestJS using TypeOrm

Question

I'm working with TypeORM and NestJS. I have two models which look like as follows.

Floor entity:

@Entity("floor")
export class FloorEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;  // floor name

  @OneToMany(() => RoomEntity, (room) => room.floor, {
    cascade: true,
    onDelete: "CASCADE"
  })
  rooms: RoomEntity[];
}

Room entity:

@Entity("room")
export class RoomEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;  // room name

  @ManyToOne(() => FloorEntity, floor => floor.rooms, {
    onDelete: "CASCADE"
  })
  floor: FloorEntity;
}

I want to perform a query, which is to get room details based on the floor and room name.

This is the expected SQL query:

select * 
from rooms 
where name = 'some name' and floor = 1

I have tried this code:

let query: FindManyOptions = {
      where: {
        name,
        floor
      },
    };

repostory.findAll(query)

This code generates the following SQL query:

SELECT 
    "RoomEntity"."id" AS "RoomEntity_id", 
    "RoomEntity"."name" AS "RoomEntity_name", 
    "RoomEntity"."createdAt" AS "RoomEntity_createdAt", 
    "RoomEntity"."updatedAt" AS "RoomEntity_updatedAt", 
    "RoomEntity"."deletedAt" AS "RoomEntity_deletedAt", 
    "RoomEntity"."floorId" AS "RoomEntity_floorId",   
    "RoomEntity"."createdBy" AS "RoomEntity_createdBy", 
    "RoomEntity"."updatedBy" AS "RoomEntity_updatedBy" 
FROM 
    "room" "RoomEntity" 
LEFT JOIN 
    "floor" "RoomEntity__RoomEntity_floor" ON "RoomEnt
ity__RoomEntity_floor"."id" = "RoomEntity"."floorId" 
                                           AND ("RoomEntity__RoomEntity_floor"."deletedAt" IS NULL) 
WHERE 
    (("RoomEntity"."name" = @0)) 
     AND ("RoomEntity"."deletedAt" IS NULL) -  PARAMETERS: ["r-1"]

I would like to know how to handle this case using TypeORM and NestJS. How can I execute this query and retrieve the results?.

How can I construct this query using TypeORM and NestJS to retrieve the desired room details based on the floor and room name?

I appreciate any help or guidance on how to achieve this. Thank you in advance!

Answer 1

Did you try to update the where clause like below

repository.find({
  where: {
    name: "roomName",
    floor: {
      name: "floorName"
    }
  },
})