SOLR travel site: on date queries

问题:

I was looking to implement SOLR for a Hotel bookings site. Search based on location, hotel names, facilities works very well and so does the faceting. What I have not been able to figure out is how to search for a hotel given Checkin and Checkout dates.

Eg: User will search for search query - "Hotels in Newyork" and select CheckIn Date: 10th Feb 2012 and CheckOut Date: 12 Feb 2012 from the date selection box.

This is how I have the data -

Hotel_Name 10thFeb2012    11thFEB2012 ........   31DEC2012
Hotel1      2room            3room                  10rooms
Hotel2      1room            4room    ........      12rooms

Now if the query is for Hotel2 for 3rooms from checkin Date 10thFeb2012 to 11thFeb2012 it shdnt match because there is only one room available for 10thFeb. IF the query for Hotel2 is for 1 room from checkin Date 10thFeb2012 to 11thFeb2012 then it should be part of search result.

回答1:

Use the ISO 8601 format for your date-times.

 Complete date plus hours, minutes, seconds and a decimal fraction of a second
 YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45Z)

Both your database and Solr will understand date-times from strings that conform to that format.

So,

1. store the data in DB and Solr with compatible date-time formats. (On the back of my head, Solr must have a Z appended to the date-time, else its invalid).
2. your search interface must format all dates in that format to query solr.

Solr can do conditional expressions, facets, range bucket faceting etc with dates.

---

I would go with the following schema:

hotel_name : string (for faceting)
hotel_name_searchable : text (for searching, this is a copy field:look it up)
room_id : string
start_date : date (when the room is availabe)
end_date : date (if not booked, set it to an infinite date, say 2040)

For each room you are ever tracking, store the date-times between which it is free. You can search for rooms between the start_date and end_date.

Do faceting on hotel_name so your search for rooms "checkin Date 10thFeb2012 to 11thFeb2012" gets you:

Hotel1:[r1,r2,r3]
Hotel2:[r8]
Hotel3:[r2,r3,r4]

Faceting on hotel_name filters to one hotel, facet.mincount on room_id can return hotels having the required number of rooms.

A little warning: I may be a bit rusty on faceting, as I used to do a lot of processing on Solr results itself.