Find open stores through timetables using Elasticsearch / Tire

Question

I have a Shop model, everyone has a relationship with Timetable , which may contain something like:

 shop_id: 1, day: 5, open_hour: 7, open_minutes: 0, close_hour: 13, close_minute: 30 shop_id: 1, day: 5, open_hour: 14, open_minutes: 30, close_hour: 18, close_minute: 00 

Of course, Timetable may have a more elegant format, but the question is: how can I use elasticsearch (tire) to find a Shop that is open?

The whole idea will be appreciated! Thanks!

---

Found a solution:

• create a separate index for each day (Sunday, Monday, ..)

• for each day to build a full array of minutes from Timetable :

   ((open_hour * 60 + open_minute)..(close_hour * 60 + close_minute)).to_a 

• add filter to search:

   filter :term, current_day_name => (current_hour * 60 + current_minutes) 

this solution works, but it looks cumbersome, because if Shop works 8 hours a day, I created an array with the size: 8 * 60 = 480 (which converts to a string as an indexed field), so therefore this question is still open, and maybe someone will find a better solution

---

The bus part for @Andrei Stefan's answer:

 indexes :open_hours, type: :nested do indexes :open, type: 'integer' indexes :close, type: 'integer' end open_hours_query = Tire::Search::Query.new do filtered do query { all } filter :range, "open_hours.open" => { lte: current_time } filter :range, "open_hours.close" => { gte: current_time } end end filter :nested, { path: 'open_hours', query: open_hours_query.to_hash } 

Answer 1

I would like to do it as follows:

• Opening and closing hours are integer values ​​of an array of nested objects in Elasticsearch:

Example: opening a store at 07:00 and closing at 13:30, and then opening at 14:30 and closing at 18:00 on the 1st day will be transferred to this in ES:

 "shop_name": "Shop 1", "open_hours": [ { "open": 420, "close": 810 }, { "open": 870, "close": 1080 } ] 

2. Each day of the week (1 → 7) represents a value (which is added to the number of minutes):

 Day 1 = addition 0 Day 2 = addition 2000 Day 3 = addition 4000 ... Day 7 = addition 10000 

So, for each day there is an increment of 2000, because each day contains no more than 1440 minutes (24 hours * 60 minutes) and in order to distinguish one day from one number, these numbers should not intersect.

So, the example above with the opening of the store at 07:00 will be translated for the 4th day, for example:

 "shop_name": "Shop 1", "open_hours": [ { "open": 6420, "close": 6810 }, { "open": 6870, "close": 7080 } ] 

3. When requesting these documents, the point of the day that you want to find must obey the same rules as above. For example, if you want to see whether “Shop 1” is open on day 4 at 13:45, you will look for a minute (6000 + 13 * 60 + 45 = 6825).

4. The mapping for everything above in Elasticsearch will be as follows:

 { "mappings": { "shop" : { "properties": { "shop_name" : { "type" : "string" }, "open_hours" : { "type" : "nested", "properties": { "open" : { "type" : "integer" }, "close": { "type" : "integer" } } } } } } } 

5. Testing data:

 POST /shops/shop/_bulk {"index":{}} {"shop_name":"Shop 1","open_hours":[{"open":420,"close":810},{"open":870,"close":1080}]} {"index":{}} {"shop_name":"Shop 2","open_hours":[{"open":0,"close":500},{"open":1000,"close":1440}]} {"index":{}} {"shop_name":"Shop 3","open_hours":[{"open":0,"close":10},{"open":70,"close":450},{"open":900,"close":1050}]} {"index":{}} {"shop_name":"Shop 4","open_hours":[{"open":2000,"close":2480}]} {"index":{}} {"shop_name":"Shop 5","open_hours":[{"open":2220,"close":2480},{"open":2580,"close":3000},{"open":3100,"close":3440}]} {"index":{}} {"shop_name":"Shop 6","open_hours":[{"open":6000,"close":6010},{"open":6700,"close":6900}]} 

6. Request for stores open on Day 2 per minute # 2400 of the day (06:40):

 { "query": { "bool": { "must": [ { "nested": { "path": "open_hours", "query": { "bool": { "must": [ { "filtered": { "filter": { "range": { "open_hours.open": { "lte": 2400 }}}}}, { "filtered": { "filter": { "range": { "open_hours.close": { "gte": 2400 }}}}} ] }}}} ] }}} 

Store 4 and Store 5 will be displayed:

  "shop_name": "Shop 4", "open_hours": [ { "open": 2000, "close": 2480 } ] "shop_name": "Shop 5", "open_hours": [ { "open": 2220, "close": 2480 }, { "open": 2580, "close": 3000 }, { "open": 3100, "close": 3440 } ]