Finding gaps within date-ranges in an associated model | Ruby on Rails

Question

Given the following models:

class Room < ActiveRecord::Base  
  has_many :contracts
end

class Contracts < ActiveRecord::Base
  # columns
  # start_date Date
  # end_date Date

  belongs_to :room
end

The contracts of one room are not overlapping. My question is, how i am be able to find gaps between contracts. An Example:

room = Room.create
c1 = room.contracts.create(:start_date => Date.today, :end_date => 1.month.since)
c2 = room.contracts.create(:start_date => 2.months.since, :end_date => 4.months.since)
rooms = Room.with_contract_gaps #rooms == [room]

The bonus round would be the possibility to search for gaps with a specific date-range or even better to fetch all gaps as date-ranges in as hash or array.

gaps = Room.contract_gaps
gaps # {1 => [((1month.since+1.day)..(2.months.since-1.day))]}

I have already searched via google and found Inverting Date Ranges. But i have honestly not really an idea, how i can use it in this specific case.

It would be great if someone has a solution or some helpful tips to solve this problem.

Answer 1

I think you want to do this as an instance method, finding the gaps between the start of the first contract, and the end of the last contract for a given room. And in answer to the "bonus" is a class method that gathers them all up. Here they are, along with a helper method for getting the date range for a set of contracts:

class Room < ActiveRecord::Base
  has_many :contracts

  # This method gets our date range
  def date_range contract_list=[]
    sorted = contract_list.sort{|a,b| a.start_date <=> b.start_date}
    Array(sorted.first.start_date..sorted.last.end_date
  end

  # This version runs when called on the class itself
  def contract_gaps
    room_hash = {}
    Room.all.each{|room| room_hash[room] = room.contract_gaps}

    room_hash
  end

  # This version runs when called on a single room
  def contract_gaps(start=nil, end=nil)
    all_dates = self.class.date_range self.contracts

    on_dates = []
    sorted_contracts.each{|c| on_dates << Array(c.start_date..c.end_date)}

    all_dates - on_dates
  end
end

And you would call it like this:

room = Room.find(1)
room.contract_gaps

This returns an array with all the dates in between that are not covered by a contract. Or call the class method to get the hash of rooms and ranges.

Answer 2

If you just need rooms with contract gaps, you could do a select like this (not tested):

SELECT rooms.*, count(*) AS count_contracts FROM rooms
  INNER JOIN contracts as c1 ON c1.room_id = rooms.id
  OUTER JOIN contracts as c2 ON c1.start_date = c2.end_date AND c2.room_id = rooms.id
WHERE c2.id IS NULL
GROUP BY rooms.id HAVING count_contracts > 1

Basically we are looking for rooms which have more than one contract that does not have the same start_date as an end_date of another contract of that room (assuming that start_date - end_date range is inclusive-exclusive).

To select just the gaps I'd probably do something like this (again, not tested):

contracts = room.contracts.all(:order => 'start_date ASC')
gaps = []
contracts[0..-2].each_with_index do |contract, idx|
  start_date = contract.end_date
  end_date = contracts[idx+1].start_date
  gaps << (start_date..end_date) if start_date != end_date
end

Answer 3

The fastest method would be to find gaps using an SQL query. However, this would be complicated and not map well to an ActiveRecord model, so the next fastest way I can think of is to sort contracts chronologically in the database and find gaps in Ruby, iterating through each result and appending rooms and dates to an array as you encounter them.

However, if you need faster access to gaps, or you're doing a lot of gap manipulation, or if these gaps are in a sense the "product" being sold, you might be better off with a different model. What about a Slot, which is the minimum possible contract length (eg, one month)? You'd create slots for every room and every month for the next few years. Each has slot.available == true to start, and the after_save callback of the Contract model sets available = false where necessary. With this setup you could define your gap-finder (Room.with_available_slots) more easily:

class Contract < ActiveRecord::Base
  has_many :slots
end

class Slot < ActiveRecord::Base
  belongs_to :room
  belongs_to :contract
end

class Room < ActiveRecord::Base
  has_many :slots
  has_many :contracts, :through => :slots

  named_scope :with_available_slots,
    :joins      => :slots,
    :conditions => {:slots => {:contract_id => nil}},
    :select     => "*, COUNT(*) AS num_slots",
    :group      => "room_id",
    :having     => "num_slots > 0"
end

This design has other useful features like the ability to prevent booking of certain dates, apply different pricing to certain slots, etc. It's not as clean as your design, but in my experience it works better with real-world data because exceptions are more easily handled.