Hey guys,
I´ve got some problem:
I just want to select all hotels in my DBase, which are completely available between an date-interval.
I´ve got an hotel-instance an within these instance i´ve got another instance called availabilities (1:N). The relationship got the cardinality of 1:N because one hotel has many availability dates. so if i get an arrival date and a departure date from an user, i just want to select all these hotels, which coverage the hole time between available date and departure date with availability (available = theres an entry in the db).
The logical problem takes place within the following line in my HQL-Query:
Code:
"AND available.date in (:arrival) " +
arrival will be replaced by a list of all dates within the arrival- and departure-date.
this code lists also hotels, which are e.g. only available at one day in the time interval, and that´s not, want i want.
This is my complete HQL-Query:
Code:
Query q = db.createHqlQuery("SELECT hotel FROM Hotel as hotel " +
"FETCH ALL PROPERTIES " +
"INNER JOIN hotel.districts as district " +
"LEFT JOIN district.location as location " +
"LEFT JOIN location.state as state " +
"LEFT JOIN state.country as country " +
"LEFT JOIN hotel.catDescriptions as catDesc " +
"LEFT JOIN hotel.availabilities as available " +
"WHERE " +
"location.id LIKE :location " +
"AND district.id LIKE :district " +
"AND state.id LIKE :state " +
"AND country.id LIKE :country " +
"AND catDesc.price BETWEEN :price1 AND :price2 " +
"AND catDesc.roomCat.id=:category " +
"AND hotel.stars BETWEEN :stars1 AND :stars2 " +
"AND available.date in (:arrival) " +
"AND available.roomCat.id=:availCategory " +
"AND hotel.deleted=:deleted");
It would be great if you could help me in a little while!
Thanking you in anticipation,
iTob87