Identifying conflicting dates on create in rails

Model:

class Reservation < ApplicationRecord
    validate :overlapping
    private
    def overlapping
    if Reservation.where('? <  date_end and ? > date_start', self.date_start, self.date_end).any?
        errors.add(:date_end, 'it overlaps another')
    end
end

Schema:

    create_table "reservations", force: :cascade do |t|
        t.date     "date_start"
        t.date     "date_end"
        t.datetime "created_at", null: false
        t.datetime "updated_at", null: false
    end

Here is rails console log after I tried to create (24 nov - 27 nov) and (25 nov - 27 nov) when there was (25 nov - 26 nov)

irb(main):003:0> Reservation.create date_start: Date.parse('25-11-2017'), date_end: Date.parse('26-11-2017')
   (0.2ms)  BEGIN
   (0.6ms)  SELECT COUNT(*) FROM "reservations" WHERE ('2017-11-25'  date_start)
  SQL (0.6ms)  INSERT INTO "reservations" ("date_start", "date_end", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["date_start", "2017-11-25"], ["date_end", "2017-11-26"], ["created_at", "2017-11-21 13:23:05.192276"], ["updated_at", "2017-11-21 13:23:05.192276"]]
   (10.7ms)  COMMIT
=> #
irb(main):004:0> Reservation.create date_start: Date.parse('24-11-2017'), date_end: Date.parse('27-11-2017')
   (0.1ms)  BEGIN
   (0.2ms)  SELECT COUNT(*) FROM "reservations" WHERE ('2017-11-24'  date_start)
   (0.2ms)  ROLLBACK
=> #
irb(main):005:0> Reservation.create date_start: Date.parse('25-11-2017'), date_end: Date.parse('26-11-2017')
   (0.1ms)  BEGIN
   (0.3ms)  SELECT COUNT(*) FROM "reservations" WHERE ('2017-11-25'  date_start)
   (0.1ms)  ROLLBACK
=> #

There are rollbacks as expected.

your validation is not working because you are checking that the new reservation dates are between the reservations dates already existing in the database. In the specific case you have mentioned, You need to check existing reservations dates are between dates in the new reservation dates.

Example mentioned in the question for reference:

Already existing reservation in the database

date_start  date_end 
26-11-2017  27-11-2017

New reservation:

date_start  date_end 
25-11-2017  28-11-2017

You need to check for the following cases:

Notation:

• R1 is the reservation already existing in the database
• R2 is the new reservation user is trying to add.
• | is representing the start date and the end date

Case1

    |------R1------|
     ____|------R2------|

when R1 starts and ends before R2

Case2

    ______|------R1------|
   |------R2------|______

when R2 starts and ends before R1

Case3

   ___|------R1------|___
   |---------R2---------|

When R2 contains R1. (The case you have mentioned in your question)

Case4

     |---------R1---------|
     __|------R2------|___

When R1 contains R2. (This is the only case you have covered in your overlapping scope)

Disclaimer: The scope below is untested and might have some issues. (You can easily write a SQL query by covering all the cases above)

    scope :overlapping, ->(period_start, period_end) do  
            where(
                "(:period_start <= date_start AND :period_end < date_end AND :period_start > :period_end) OR
                 (:period_start >= date_start AND :period_end > date_end AND :period_start < date_end) OR
                 (:period_start < date_start AND :period_end >= date_end) OR
                 (:period_start >= date_start AND :period_end <= date_end)",period_start: period_start, period_end: period_end)
        end

• There will be many dates on you reservations table. for example:

  • reservation one: 7.days.from_now ~ 6.days.from_now
  • reservation two: 3.days.from_now ~ 1.days.from_now

• Which means you have to recognize the reserved date by each range.

  • originally: 1~3, 6~7

• I used a hash to index reserved dates, like this:

  • not available dates (from now): 1, 2, 3, 6, 7
  • also, you can create efficient index narrowed by searching (or scoping) only the target date_start and date_end or active reservations

Model:

class Reservation < ApplicationRecord
    validate :exclusive_reservation?

    def exclusive_reservation?
        result = true
        reserved = {}

        Reservation.pluck(:date_start, :date_end).each do |date_range|
            (date_range.first..date_range.second).each do |date| 
                reserved[date] = true
            end
        end

        if reserved.has_key? self.date_start
            errors.add(:date_start, 'it overlaps another reservation')
            result = false
        end

        if reserved.has_key? self.date_end
            errors.add(:date_end, 'it overlaps another reservation')
            result = false
        end

        result
    end
end