JPQL checkif room is available between dates

Question

I have a Calendar and a Room Entity.

My Calendar entity looks like this:

public class CalendarEntity {

    @EmbeddedId
    private CalendarId calendarId = new CalendarId();

    @Basic
    @Column(name = "available", nullable = false)
    private Boolean available;

    @Basic
    @Column(name = "price")
    private double price;

    @ManyToOne
    @JoinColumn(name = "room_id", referencedColumnName = "id", insertable = 
    false, updatable = false)
    private RoomEntity roomEntity;

}
    @Embeddable
    public class CalendarId implements Serializable {

        @Column(name= "date", nullable = false)
        private Date date;

        @Column(name = "room_id", nullable = false)
        private Integer room_id;
}

My Room Entity looks like this:

public class RoomEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;
}

Let's say i have some entries in the calendar entity for dates 2017-10-5, 2017-10-6, 2017-10-7, 2017-10-8 with room_id=10.

I want to construct a query so that if the user asks for a room with a certain checkin and checkout date, then he will get the right list of the rooms.

How can i do that?

For example the user asks for the room with checkin=2017-10-6 and checkout=2017-10-8 and the room should appear in the list.

What the query should be so i get the right list of the rooms?

UPDATE:

The query i have for now only check if the room is available on the checkin date and the checkout date but not in between:

select r from RoomEntity r where r.id in (Select c1.calendarId.room_id from CalendarEntity c1, CalendarEntity c2 where (c1.calendarId.room_id = c2.calendarId.room_id)  and (c1.calendarId.date =:checkin and c1.available=true) and(c2.calendarId.date =:checkout and c2.available=true))

I do not think it is the same. What i want is, to check if every date from checkin date until checkout date is available. And if it is, then get the room_id. — peterthunder, Jul 11 '17 at 18:15
The answer to your question is a small variation to the question I referenced, all you need to do is use a subquery with `NOT EXISTS`... — crizzis, Jul 11 '17 at 19:06

OTM · Accepted Answer · 2017-07-11T18:57:26.043

1

An approach is: SELECT count(c.calendarId.room_id), c.calendarId.room_id FROM CalendarEntity c WHERE c.available = true and c.calendarId.date BETWEEN :checkin and :checkout GROUP BY c.calendarId.room_id HAVING count(c.calendarId.room_id) >= :noOfDays noODays denotes the days calculated from checkin and check out dates for which the booking is required.

edited Jul 11 '17 at 18:57

answered Jul 11 '17 at 18:51

OTM

656
5
8

score 0 · Answer 2 · answered Jul 11 '17 at 19:05

SELECT r FROM RoomEntity r 
WHERE r.id 
in (select c1.calendarId.room_id 
    FROM CalendarEntity c1 JOIN CalendarEntity c2 
    WHERE c1.calendarId.room_id = c2.calendarId.room_id  
    AND ((c1.calendarId.date BETWEEN :checkin AND :checkout) 
         AND (c1.available=true and c2.available=true));

JPQL checkif room is available between dates

2 Answers2