Crisscrossing date range queries in Rails

If you are building an application that takes reservations or searches for availability, you're likely to want to query against booked date ranges to find if the candidate date range is available, or if it conflicts with an existing booking.

There are various ways to find overlapping date ranges with SQL, using the SQL OVERLAPS operator, or a custom query, as described (in detail) by depesz on http://www.depesz.com: http://www.depesz.com/index.php/2007/11/21/find-overlapping-time-ranges/

In the past I might have written a method like this in my model to find available units (more often than not combined with other search criteria):

 @rooms = Room.find_by_sql ["
     SELECT * FROM rooms WHERE rooms.id NOT IN
     (SELECT DISTINCT ON (rooms.id) rooms.id FROM bookings
     INNER JOIN rooms ON rooms.id=bookings.rooms_id
     WHERE ((start_date, end_date) OVERLAPS (DATE '#{start_date[0]}',
     INTERVAL '#{interval} days') = true;"]

Enter acts_as_line, a simple plugin that unifies a date range into a single geometry and looks for other ranges (now lines) that spatially intersect with it.

I am putting this together for querying larger datasets with several million date ranges, where OVERLAPS may feel the squeeze. The utility of using this method for small datasets is questionable, but it is trivial to extend a Postgres database with PostGIS, and I think I'll find the plugin more convenient than writing OVERLAPS queries. It certainly shouldn't be slower. I've yet to benchmark it against OVERLAPS on large, indexed tables, but I expect it to be quicker.

In its current form, the plugin ssumes the model has existing date fields, start_date and end_date, of type date or datetime, and a geometry column named 'geom'. There is a migration generator to add the dates geometry to an existing table (providing the postgres database is spatially enabled). I will be adding options to specify alternate field names via the model soon, and will probably rename the geometry field to date_geom to minimise the chances of conflict with existing schemas under the defaults (although I don't anticipate anyone storing other, geographic, geometries on the same table as the one storing date ranges).

Adding acts_as_line to a model,

AgencyBooking < ActiveRecord::Base

  acts_as_line

end

makes the following methods available:

object = AgencyBooking.find(:first)

results = AgencyBooking.touching(object) | results = Foo.intersects(object,true)
results = AgencyBooking.asunder(object) | results = Foo.intersects(object,false)

results = AgencyBooking.touching(object,{:id => 123})
results = AgencyBooking.touching(object,{:id => '>123'})
results = AgencyBooking.touching(object,{:id => '<>123'}) etc
results = AgencyBooking.touching(object,{:id => 123, :title => 'bar'})

Some usage examples


create a new record:

>> booking = AgencyBooking.new(:start_date => Date.today, :end_date => Date.today+6.days)
=> #<AgencyBooking id: nil, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-07", end_date: "2008-03-13", created_at: nil, updated_at: nil, geom: nil>
>> booking.save
=> true
>> booking
=> #<AgencyBooking id: 12746, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-07", end_date: "2008-03-13", created_at: "2008-03-07 10:05:45", updated_at: "2008-03-07 10:05:45", geom: "0102000000020000000000000000000000000000803DF4D1410...">


find overlapping records:

>> AgencyBooking.touching(booking)
=> [#<AgencyBooking id: 12740, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-01 00:00:00", end_date: "2008-03-11 00:00:00", created_at: "2008-03-06 16:00:00", updated_at: "2008-03-06 16:00:00", geom: "01020000000200000000000000000000000000004043F2D1410...">, #<AgencyBooking id: 12746, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-07 00:00:00", end_date: "2008-03-13 00:00:00", created_at: "2008-03-07 10:05:45", updated_at: "2008-03-07 10:05:45", geom:
"0102000000020000000000000000000000000000803DF4D1410...">]

find records that do not overlap:

>> AgencyBooking.asunder(booking)
=> [#<AgencyBooking id: 12741, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-01 00:00:00", end_date: "2008-03-02 00:00:00", created_at: "2008-03-06 16:00:02", updated_at: "2008-03-06 16:00:02", geom: "01020000000200000000000000000000000000004043F2D1410...">, #<AgencyBooking id: 12742, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-01 00:00:00", end_date: "2008-03-01 00:00:00", created_at: "2008-03-06 16:00:04", updated_at: "2008-03-06 16:00:04", geom: "01020000000200000000000000000000000000004043F2D1410...">, #<AgencyBooking id: 12743, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-21 00:00:00", end_date: "2008-03-23 00:00:00", created_at: "2008-03-06 16:01:41", updated_at: "2008-03-06 16:01:41", geom: "01020000000200000000000000000000000000003CD7F8D1410...">, #<AgencyBooking id: 12744, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-21 00:00:00", end_date: "2008-03-21 00:00:00", created_at: "2008-03-06 16:02:28", updated_at: "2008-03-06 16:02:28", geom: "01020000000200000000000000000000000000003CD7F8D1410...">, #<AgencyBooking id: 12745, agency_id: nil, agency_unit_id: nil, start_date: "2008-03-01 00:00:00", end_date: "2008-03-06 00:00:00", created_at: "2008-03-06 18:00:47", updated_at: "2008-03-06 18:00:47", geom: "01020000000200000000000000000000000000004043F2D1410...">]


You can grab the initial release from github:

acts_as_line

I still have much to add and refine.