Rails validate uniqueness of date ranges

Question

I have an application that involves absence records for employees.

I need to ensure that the start and end dates for each record don't overlap.

So for example, if I entered an absence record that started today and ended tomorrow, it shouldn't be possible to enter another inside of that date range in any way. So I couldn't make one that starts the day before today, then ends the day after tomorrow, or any later date.

To put it simply, I need to make the date range unique.

What is the best way to achieve this?

Custom validators in the model class that involve iterating through all records take far too long to complete, and I haven't found any gems that address this problem. I also haven't found any simply way to scope by uniqueness in the model either. I'm stumped :/

Thanks for your time!

Edit:

class Absence < ActiveRecord::Base
attr_accessible :date, :date_ended, :status, :reason, :form, :user_id, :tempuser, :company_id
belongs_to :user
default_scope { where(company_id: Company.current_id) }

validates :date, :date_ended, :status, :reason, :form, :user_id, presence: true 
validates_numericality_of :user_id, :only_integer => true, :message => "can only be whole number."
end

Answer 1

I use these:

  scope :overlaps, ->(start_date, end_date) do
    where "(DATEDIFF(start_date, ?) * DATEDIFF(?, end_date)) >= 0", end_date, start_date
  end

  def overlaps?
    overlaps.exists?
  end

  # Others are models to be compared with your current model
  # you can get these with a where for example
  def overlaps
    siblings.overlaps start_date, end_date
  end

  validate :not_overlap

  def not_overlap
    errors.add(:key, 'message') if overlaps?
  end

  # -1 is when you have a nil id, so you will get all persisted user absences
  # I think -1 could be omitted, but did not work for me, as far as I remember
  def siblings
    user.absences.where('id != ?', id || -1)
  end

Source: https://makandracards.com/makandra/984-test-if-two-date-ranges-overlap-in-ruby-or-rails

Answer 2

As a modification to the accepted answer, here's an overlaps scope that will work for DBs that don't understand DATEDIFF

  scope :overlaps, ->(start_date, end_date) do
    where "((start_date <= ?) and (end_date >= ?))", end_date, start_date
  end

This draws on the solution for Determine Whether Two Date Ranges Overlap