CODEWITHSUNDEEP
javamysqlsqlhibernatejpa
twistezo
twistezo

Reputation: 576

HQL / JPA find available items between date range

It's car rental app. I have table:

+-------------------------------------+
|id  |start_date  |end_date    |car_id| 
+----+------------+------------+------+  
|1   |2017-04-01  |2017-04-10  |1     |
|2   |2017-04-15  |2017-04-20  |1     |
|3   |2017-04-15  |2017-04-20  |2     |
+----+------------+------------+------+

Hibernate query for find all available cars between user input 'userDateStart' and 'userDateEnd'.

select
b.id, b.startDate, b.endDate, b.car.id
from BorrowedDate as b
where :userDateStart not between startDate and endDate
and :userDateEnd not between startDate and endDate

------------
:userDateStart = 2017-04-02 00:00:00
:userDateEnd = 2017-04-10 00:00:00

My result is:

+-------------------------------------+
|id  |start_date  |end_date    |car_id| 
+----+------------+------------+------+  
|2   |2017-04-15  |2017-04-20  |1     |
|3   |2017-04-15  |2017-04-20  |2     |
+----+------------+------------+------+

Result is wrong because car with id=1 is borrowed in this time. Only car_id=2 should be available.

Upvotes: 1

Views: 2838

Answers (2)

mibrahim.iti
mibrahim.iti

Reputation: 2060

I see your query is correct.

But if you want to select based on car id so you should add car id as a condition too into your query

For example

SELECT b.id, b.startDate, b.endDate, b.car.id
FROM BorrowedDate b
WHERE :userDateStart NOT BETWEEN b.startDate AND b.endDate
AND :userDateEnd NOT BETWEEN b.startDate AND b.endDate
AND b.car.id = :carId

OR you can do it like that too

SELECT b.id, b.startDate, b.endDate, c.id
FROM BorrowedDate b INNER JOIN b.car c
WHERE :userDateStart NOT BETWEEN b.startDate AND b.endDate
AND :userDateEnd NOT BETWEEN b.startDate AND b.endDate
AND c.id = :carId

then you have to pass :carId value equals 2

UPDATE

you can try this SQL query if you need to get result dynamic, i will update it soon with JPQL

SELECT b.id, b.startDate, b.endDate, b.car.id
FROM BorrowedDate b
WHERE '2017-04-02 00:00:00' NOT BETWEEN b.startDate AND b.endDate
AND '2017-04-10 00:00:00' NOT BETWEEN b.startDate AND b.endDate
AND b.car.id NOT IN (SELECT DISTINCT bd.car.id FROM BorrowedDate bd WHERE '2017-04-02 00:00:00' BETWEEN bd.startDate AND bd.endDate OR '2017-04-10 00:00:00' BETWEEN bd.startDate AND bd.endDate)

HERE are JPQL too

SELECT model.id, model.startDate, model.endDate, model.car.id
FROM BorrowedDate model
WHERE :userDateStart NOT BETWEEN model.startDate AND model.endDate
AND :userDateEnd NOT BETWEEN model.startDate AND model.endDate
AND model.car.id NOT IN (SELECT DISTINCT b.car.id FROM BorrowedDate b WHERE :userDateStart BETWEEN b.startDate AND b.endDate OR :userDateEnd BETWEEN b.startDate AND b.endDate)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you are looking for a fully overlapping period. This is the correct logic for full overlaps:

select b.id, b.startDate, b.endDate, b.car.id
from BorrowedDate as b
where :userDateStart <= startDate and
      :userDateEnd >= endDate;

So, this gets you cars that are rented for the entire period that the customer wants one. That is probably not your intention.

Let me assume you want cars available during the period. For that, let me assume that you have a cars table:

select c.*
from cars as c
where not exists (select 1
                  from borrowed b
                  where :userDateStart <= endDate and
                        :userDateEnd >= startDate
                 );

Note: This is standard(ish) SQL. Hibernate has some unusual conventions, but the idea should be the same.

Upvotes: 0

Related Questions