Блог

У меня есть две таблицы, а именно Room amp; Allotment. Комната занята. И в выделении я храню человека, который занимает комнату. Теперь я хочу узнать, сколько мест доступно в каждой комнате.

Сущности

 @Table(name = "ALLOTMENT")
public class RoomAllotment {
    @Id
    @Column(name = "ALLOT_NO")
    private Integer allotNo;
    
    @OneToOne
    @JoinColumn(name="RM_CD", referencedColumnName = "RM_CD")
    private RoomMaster roomCode;
   
    //some more columns
}

@Table(name = "ROOMS")
public class RoomMaster {
    @Id
    @Column(name = "RM_CD")
    private Integer roomCode;
    
    @Column(name = "ROOM_NO")
    @NotNull(message = "Room number is required")
    @Size(max=20, message = "Maximum 20 characters only")
    private String roomNo;
    
    @Column(name = "OCCUPANCY")
    @Range(min=1, max=10, message = "Occupancy can be minimum 1 or maximum 10 only")
    private Integer occupancy;

    //some more columns
}
  

В базе данных у меня есть следующие значения.

 Room Table

RM_CD | ROOM_NO | OCCUPANCY |
-----------------------------
    1 |   A-401 |         5 |
    2 |   A-402 |         4 |

Allotment Table

ALLOT_NO | RM_CD |   OCCUPANT  |
--------------------------------
       1 |     1 |  Alex Warne |
       2 |     1 | Mike Parker |
  

Теперь мне нужны все комнаты с текущим заполнением.
Комната 1 -> 5 — (два человека живут в комнате 1) = 3
Комната 2 -> 4 — (в комнате 2 еще никто не живет) = 4

Как я могу написать запрос для этого. Я написал один запрос, но он выдает ошибку.

 @Query(value = "SELECT r.roomCode, " 
                "(r.occupancy- " 
                "(SELECT COUNT(*) FROM RoomAllotment ra WHERE ra.roomCode.roomCode=r.roomCode) " 
                ") occupancy" 
                "FROM RoomMaster r " 
                "ORDER BY r.roomCode")
Page<RoomDetails> findAllVacantRooms(Pageable pageable);
  

Ошибка:

 Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: occupancy near line 1, column 168 [SELECT r.roomCode, (r.occupancy- (SELECT COUNT(*) FROM com.datanet.model.osm.RoomAllotment ra WHERE ra.roomCode.roomCode=r.roomCode) )occupancy FROM com.datanet.model.osm.RoomMaster r ORDER BY r.roomCode]
  

Вы можете использовать left outer join и group by следующим образом:

 SELECT R.roomCode, R.occupancy - COUNT(A.allotNo) AS occupancy 
  FROM ROOM R LEFT JOIN ALLOTMENT   A
    ON R.roomCode= A.roomCode
 GROUP BY R.roomCode, R.occupancy