+972 52-548-6969
Is the library open or not?
An interesting challenge came across my desk. Let us assume that we have a set of libraries, which looks like this:
{
"Name": "The Geek Hangout",
"OpeningHours": {
"Sunday": [
{ "From": "08:00", "To": "13:00" },
{ "From": "16:00", "To": "19:00" }
],
"Monday": [
{ "From": "09:00", "To": "18:00" },
{ "From": "22:00", "To": "23:59" }
],
"Tuesday": [
{ "From": "00:00", "To": "04:00" },
{ "From": "11:00", "To": "18:00" }
]
}
}
|
{
"Name": "Beer & Books",
"OpeningHours": {
"Sunday": [
{ "From": "16:00", "To": "23:59" }
],
"Monday": [
{ "From": "00:00", "To": "02:00" },
{ "From": "10:00", "To": "22:00" }
],
"Tuesday": [
{ "From": "10:00", "To": "22:00" }
]
}
}
|
I only included three days, to make it shorter, but you get the points. You can also see that there are times that the opening hours go through a day.
Now, the question we need to answer is: “find me an open library now”.
How can we answer such a question? If we were using SQL, it would be something like this:
select * from Libraries l where Id in ( select Library Id OpeningHours oh where oh.Day = dayofweek(now()) AND oh.From >= now() AND oh.To < now() )
I’ll leave the performance of such a query to your imagination, but the key point is that we cannot actually express such a computation in RavenDB. We can do range queries, but in this case, it is the current time that we compare to the range of values. So how do we answer such a query?
As usual, but not trying to answer the same thing at all. Here is my index:
The result of this is an index entry per day, and in each index entry, we have outputted the half hours that this library is open. So if we want to check for libraries that are open on Sunday at 4:30 PM, all we have to do is issue the following query:
The power of dynamic fields and index time computation means that this is an easy query to make, and even more importantly, this is something that we can answer very efficiently.
Comments
When I saw the post title yesterday, I thought you were going to talk about .NET Core Libraries going open source :).
I like it. Takes me a while to parse the LINQ query as I read it.
but also you've reduced the resolution to 30 minutes, what if the ranges had arbitrary-precision boundaries?
Rafal, You can go all the way to a single minute, which should be probably good enough. In practice, one hour resolution is good enough. There are very few libraries that are opened from 7:07 AM to 16:42 PM
For each record being mapped, you create 7 index entries. Didn't 3.0 recently add a limit to the number of index entries a single map can project (making it silently fail to index some records)? Seems like this technique will only work for small combinations.
Paul, The default limit is 15, and you can explicitly configure it to be higher if you need to, so this is relevant for more than just small collections
Only caveat is that approach wont work if your resolution is higher and your data grows linearly. Codealike is all about those types of queries and we are reaching 150000 hs of detailed activity at the ms level. That approach would mean that even if we do that at the minute level you still have to deal (at a minimum) with 9 million entries in the index and it will continue growing.
So to summarise :)
Comment preview