java - 如何在 spring jpa 中组合 4 个表(查询)

Question

我有一个名为RoomBoy的数据表，数据是这样的

"room": {
            "id": 1,
            "roomId": 40,
            "floor": "1",
            "roomNumber": "107",
            "condition": null,
            "status": "Waiting"
        },
        "bedType": {
            "id": 1,
            "bedTypeName": "Twin Bed"
        },
        "roomCategory": {
            "id": 35,
            "categoryName": "Superior"
        },
        "guestName": "RIKO JANUAR"
    }

我还有一个名为 RoomBoyDetail 的表中的数据

    {
        "id": 1,
        "roomBoyId": 1,
        "productId": 3,
        "productName": {
            "id": 3,
            "productName": "HORS D’OEUVRE VARIE"
        },
        "qty": 3,
        "description": null
    },
    {
        "id": 2,
        "roomBoyId": 1,
        "productId": 4,
        "productName": {
            "id": 4,
            "productName": "CREAM OF VEGETABLES SOUP"
        },
        "qty": 2,
        "description": null
    },
    {
        "id": 3,
        "roomBoyId": 1,
        "productId": 5,
        "productName": {
            "id": 5,
            "productName": "RAVIOLI AU JUS"
        },
        "qty": 2,
        "description": null
    }

我想合并这两个表，它们具有相同的 id(来自 RoomBoy)和 roomBoyId(来自 RoomBoyDetail)。我的代码是这样的

@Query("select new com.acs.pms.model.ObjekRoomBoyDetail(a,b.bedType,b.roomCategory,c.guestName,d.productName,d.qty,d.description) from RoomBoy a left outer join RoomMaster b on a.roomId = b.id "
        + "left outer join ReservationDetail c on a.roomNumber = c.roomNumber where c.checkinStatus = true and c.checkoutStatus = false "
        + "left outer join RoomBoyDetail d on a.id = d.roomBoyId")
        Page<ObjekRoomBoyDetail> getAllDetail(
        Pageable paging);

我有错误的意外标记:在第 1 行附近离开

Answer 1

在 where 子句之后有一个左外连接:

select new com.acs.pms.model.ObjekRoomBoyDetail(a,b.bedType,b.roomCategory,c.guestName,d.productName,d.qty,d.description) 
from RoomBoy a 
left outer join RoomMaster b on a.roomId = b.id 
left outer join ReservationDetail c on a.roomNumber = c.roomNumber 
where c.checkinStatus = true and c.checkoutStatus = false 
left outer join RoomBoyDetail d on a.id = d.roomBoyId

应该是

select new com.acs.pms.model.ObjekRoomBoyDetail(a,b.bedType,b.roomCategory,c.guestName,d.productName,d.qty,d.description) 
from RoomBoy a 
left outer join RoomMaster b on a.roomId = b.id 
left outer join ReservationDetail c on a.roomNumber = c.roomNumber 
left outer join RoomBoyDetail d on a.id = d.roomBoyId
where c.checkinStatus = true and c.checkoutStatus = false