How to validate overlapping times in Rails

I have an Event model that has form time and to time in my schedule app and I want to validate the overlapping time before saving.

My view image as followings;

Departure date: Dec 31, 2016

Day1
07:00 - 07:20 event1
10:30 - 11:30 event2
15:40 - 16:10 event3
[add event button]

Day2
08:15 - 09:05 event4
12:08 - 13:04 event5
14:00 - 14:25 event6
[add event button]

[save schedule button]

from time and to time can be changed and added at the same time.

What I'd like to do is to display error if I try to add (or change to) 07:05 - 07:30 for Day1, for example, 13:50 - 14:30 for Day2 and so on.

Although I tried some codes with overlap, between, cover with referring to this post or this post and so on, I haven't be able to apply them to my code.

schema.rb

  create_table "events", force: :cascade do |t|
    t.time     "from"
    t.time     "to"
    t.integer  "room_id"
    ...

  create_table "rooms", force: :cascade do |t|
    t.integer  "schedule_id"
    ...

  create_table "schedules", force: :cascade do |t|
    t.integer  "user_id"
    t.date     "departure_date"
    ...

Give the following models:

class Schedule < ActiveRecord::Base
  belongs_to :user
  has_many :rooms, inverse_of: :schedule
  accepts_nested_attributes_for :rooms, allow_destroy: true
  ...

class Room < ActiveRecord::Base
  belongs_to :schedule, inverse_of: :rooms
  has_many :events, inverse_of: :room
  accepts_nested_attributes_for :events, allow_destroy: true
  ...

class Event < ActiveRecord::Base
  belongs_to :room, inverse_of: :events
  has_one :schedule, autosave: false, through: :room
  ...

_schedule_form.html.erb

  <%= render 'shared/error_messages', object: f.object %>
  <%= f.label :title %>
  <%= f.text_field :title, class: 'form-control' %>
  <br>
    <%= f.label :departure_date %>
    <div class="input-group date" id="datetimepicker">
      <%= f.text_field :departure_date, :value => (f.object.departure_date.strftime('%b/%d/%Y') if f.object.departure_date), class: 'form-control' %>
      <span class="input-group-addon">
        <span class="glyphicon glyphicon-calendar"></span>
      </span>
    </div>
  <script type="text/javascript">
    $(function () {
      $('#datetimepicker').datetimepicker({format:'MMM-DD-YYYY'});
    });
  </script>
  <br>
  <div id="room">
    <%= f.simple_fields_for :rooms do |a| %>
      <div id="room_<%= a.object.object_id %>">
        <p class="day-number-element-selector"><b>Day&nbsp;<%= a.index.to_i + 1 %></b></p>

        <%= a.simple_fields_for :events do |e| %>
          <span class="form-inline">
            <p>
              <%= e.input :from, label: false %>&nbsp;&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;
              <%= e.input :to, label: false %>
            </p>
          </span>
          <%= e.input :title, label: false %>
        <% end %>
      </div>

      <%= a.link_to_add "Add event", :events, data: {target: "#room_#{a.object.object_id}"}, class: "btn btn-primary" %>

      <%= a.input :room %>

    <% end %>
  </div>

It would be appreciated if you could give me how to check and display error.

EDIT!

still not work

event.rb

class Event < ActiveRecord::Base
  before_save :assign_date
  belongs_to :room, inverse_of: :events
  has_one :schedule, autosave: false, through: :room
  validate :cannot_overlap_another_event

scope :in_range, -> range {
  where('(\'from\' BETWEEN ? AND ?)', range.first, range.last)
}
scope :exclude_self, -> id { where.not(id: id) }

def cannot_overlap_another_event
  range = Range.new from, to
  overlaps = Event.exclude_self(id).in_range(range)
  overlap_error unless overlaps.empty?
end

def overlap_error
  errors.add(:overlap_error, 'There is already an event scheduled in this hour!')
end

development.log

....
Started POST "/schedules" for 218.33.213.91 at 2016-04-07 11:44:59 +0000
Processing by SchedulesController#create as HTML
...
      [1m[35m (0.5ms)[0m  begin transaction
      [1m[36m (0.5ms)[0m  [1mSELECT COUNT(*) FROM "events" WHERE ("events"."id" IS NOT NULL) AND (('from' BETWEEN '2016-04-07 07:00:00.000000' AND '2016-04-07 07:20:00.000000'))[0m
      [1m[35m (0.3ms)[0m  SELECT COUNT(*) FROM "events" WHERE ("events"."id" IS NOT NULL) AND (('from' BETWEEN '2016-04-07 07:05:00.000000' AND '2016-04-07 07:30:00.000000'))
      [1m[36mSQL (0.5ms)[0m  [1mINSERT INTO "schedules" ("title", "departure_date", "user_id", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?)[0m  [["title", "test title"], ["departure_date", "2016-04-10"], ["user_id", 1], ["created_at", "2016-04-07 11:45:00.061460"], ["updated_at", "2016-04-07 11:45:00.061460"]]
...

回答1:

Well, if you need a server side validation, you could implement some custom validators in your model class:

validate :cannot_overlap_another_event

Next you need to code this method yourself:

def cannot_overlap_another_event
  range = Range.new from, to
  overlaps = Appointment.exclude_self(id).in_range(range)
  overlap_error unless overlaps.empty?
end

Explaining what this code do, you create a Range object with your from and to dates. Then it uses the helper scopes to exclude the Event itself and check to see if there's an event in this range.

scope :in_range, -> range {
  where('(from BETWEEN ? AND ?)', range.first, range.last)
}
scope :exclude_self, -> id { where.not(id: id) }

The overlap_error is a method that populates the model's error hash to display on screen:

def overlap_error
  errors.add(:overlap_error, 'There is already an event scheduled in this hour!')
end

回答2:

Take a look at this https://makandracards.com/makandra/984-test-if-two-date-ranges-overlap-in-ruby-or-rails. The key is to define one scope to chek for overlapping siblings. Somthing like this:

# Return a scope for all interval overlapping the given interval, including the given interval itself
  named_scope :overlapping, lambda { |interval| {
    :conditions => ["id <> ? AND (DATEDIFF(start_date, ?) * DATEDIFF(?, end_date)) >= 0", interval.id, interval.end_date, interval.start_date]
  }}

Also you can check this gem: https://github.com/robinbortlik/validates_overlap I think it can help.

回答3:

In your EventsController#Create & EventsController#Update methods check for new time in between the old times like :

events = Event.where(:departure_date => event_params[:departure_date])
events.each do |event|
 if event_params[:to].between?(event.to, event.from) || event_params[:from].between?(event.to, event.from)
  flash[:error] = "Current Slot Time is already taken!"
  redirect_to :back
  return
 end
end