Availability date range queries in app engine datastore?

Question

I've looked through a bunch of other threads on this topic, and so far I haven't found any satisfactory answers, so I wanted to double check if it's really just not feasible with the datastore.

In the datastore I have a set of entities that can be booked for particular time periods, and thus they have availability ranges attached to them. An AvailabilityRange entity has a start and end date, and I want to find all the AvailabilityRanges that completely encompass a desired date range. In SQL the where clause would look something like:

WHERE AvailRange.startDate < :desiredStartDate AND AvailRange.endDate > :desiredEndDate

With the datastore this isn't possible because you can't have inequality filters on multiple properties. Also, I saw some old comments about storing the start and end date in a single multivalued list property (so then you'd have inequality filters on a single property), but this doesn't work in app engine - see #3 on this blog post: http://aleatory.clientsideweb.net/2009/11/28/google-app-engine-datastore-gotchas/ .

I've also seen advice that recommends, for example, storing all of the available days in a list property so you can then do straight equality filters on that list property, but that solution doesn't really work if you need down-to-the-minute granularity on your ranges.

Thus, right now it seems like this just isn't possible with the datastore. My workaround plan for now is to use the App Engine Search service to store availability range documents (because the search service DOES allow multiple inequality filters), and then map those back to entities in the datastore.

Anyone have any better advice?

Answer 1

If you have a relatively small number of entities (not in millions), you can run two keys-only queries - one for start date and one for end date, and then find the intersection of results. Keys-only queries are almost free and very fast.

If there are some other limitations on your data, you can use them to optimize this process. For example, if the availability range has a duration limit, you can loop through the start-date query until you reach a date at which an entity can no longer be available, because the start date is too far away from the desired end date. Then do the same with the end date query.

Answer 2

Use the search api as you suggest. I had the same problem of multiple inequalities and slso used search api to solve it.