Hi all,
I have got a project using hibernate JPA, Spring, Hibernate Search frameworks, and got a model structure which represents a simple scheduling/timetable facility. In short my related model look like this:
Code:
schedule {
valid - the date when this individual schedule is valid
stops - schedule stops, where the actual vehicle stops (collection)
}
stop {
schedule_id - owning schedule id
arrive - hh:mm when the vehicle arrives to the station
departure - hh:mm when the vehicle departures from this station
station - Station name
}
I would like to use hibernate search's full text search capability to be able to rapidly search for schedules based on various criteria. I wrote a JPQL / SQL script which looks up schedules based on this pseudo query:
Give me schedules from station X to Y at Z (departing around X1 and arriving at Y1) the part in the parenthesis is optional, does not really matter right now. The previous query's key point is the
from X to Y point which means the direction of the schedule. The direction is calculated by a simple formula: departure time must be less than arrival time, simply not? :) X to Y is not equal with Y to X.
In SQL it simply looks like something like this (without the optional part for simplicity):
Code:
select * from schedule s where s.valid = 'valid day eg 2013-09-28' and s.id in (select ss1.schedule_id from schedule_stop ss1 inner join schedule_stop ss2 on ss1.schedule_id = ss2.schedule_id where ss1.station = 'Departure station name' and ss2.station = 'Arrival station name' and ss1.departure < ss2.arrival)
There is nothing wrong with this query, executes under 100ms in a 1 million schedue with 5-6 million stops database. The problem comes in when I want to make queries against station names with a 'like' keyword, accent dependent names...etc...etc
So because of this, I added Hibernate Search to my project and set it up successfully. Made a simple lucene query with hibernate's query builder I was able to retrieve schedules based on various criterias, the problem is I was not able to filter by direction (the ss1.departure < ss2.arrival clausure). So finally my question is: How can i somehow determine the direction of an individual schedule.
Login
FAQ
Search