SamuraiBlue SamuraiBlue - 7 months ago 82
Ruby Question

How to validate overlapping times in Rails with postgresql

I have an

Event model
that has
start_at
time and
end_at
time in my schedule app and I want to validate the overlapping time before saving.

I create my rails app on Cloud9.

My view image as followings;

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]


start_at
time and
end_at
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.

For example;

app_development=# select * from events;

id | start_at | end_at | title | detail | schedule_id | created_at | updated_at
----+----------+----------+--------+--------+-----------------+----------------------------+----------------------------
1 | 07:00:00 | 07:20:00 | event1 | | 1 | 2016-04-12 05:28:44.166827 | 2016-04-12 12:52:07.682872
2 | 10:30:00 | 11:30:00 | event2 | | 1 | 2016-04-12 05:28:44.17747 | 2016-04-12 12:52:07.689934
3 | 15:40:00 | 16:10:00 | event3 | | 1 | 2016-04-12 05:29:07.5005 | 2016-04-12 12:52:07.693477


I added
07:05 - 07:30
above table, but the validation doesn't work.

Although I asked the similar question, I was advised to use postgresql instead of sqlite3.

So I managed to configure postgresql, but the result is the same.
It would be appreciated if you could give me how to check and display error.

schema.rb

create_table "events", force: :cascade do |t|
t.time "start_at"
t.time "end_at"
t.string "title"
t.integer "room_id"
...

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

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


Give the following models:

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

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

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

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

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
...


_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 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:'YYYY-MM-DD'});
});
</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 :start_at, label: false %>&nbsp;&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;
<%= e.input :end_at, 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

edit as followings;

event.rb

scope :in_range, -> range {
where('(start_at BETWEEN ? AND ? OR end_at BETWEEN ? AND ?) OR (start_at <= ? AND end_at >= ?)', range.first, range.last, range.first, range.last, range.first, range.last)
}


Although it seems to work, this validate doesn't work when I add
event
on a different day as following
id=8
. (see
created_at
and
updated_at
)

app_development=# select * from events;

id | start_at | end_at | title | detail | room_id | created_at | updated_at
----+----------+----------+--------+--------+-----------------+----------------------------+----------------------------
1 | 07:00:00 | 07:20:00 | event1 | | 1 | 2016-04-12 05:28:44.166827 | 2016-04-12 12:52:07.682872
2 | 10:30:00 | 11:30:00 | event2 | | 1 | 2016-04-12 05:28:44.17747 | 2016-04-12 12:52:07.689934
3 | 15:40:00 | 16:10:00 | event3 | | 1 | 2016-04-12 05:29:07.5005 | 2016-04-12 12:52:07.693477
8 | 07:05:00 | 07:10:00 | event4 | | 1 | 2016-04-15 21:37:58.569868 | 2016-04-15 21:39:27.956737

Answer

Your scope is headed in the right direct, but doesn't cover all your cases.

scope :in_range, -> range {
  where('(start_at BETWEEN ? AND ?)', range.first, range.last)
}

In your example, you end up checking start_at BETWEEN 7:05 AND 7:30, but start_at on Day 1 is 7:00, which is outside that range.

There are four cases you need to handle:

New range overlaps start
Existing:     |------------|
New:      |-------|

New range overlaps end
Existing: |------------|
New:               |-------|

New range inside existing range
Existing: |------------|
New:         |-------|

Existing range inside new range
Existing:    |-------|
New:      |------------|

Looking, you can see that the first three cases are handled by checking if

new_start BETWEEN start_at AND end_at
OR
new_end   BETWEEN start_at AND end_at

Then you just need to catch the fourth case by adding

OR
start_at BETWEEN new_start AND new_end

You could add a similar check on end_at for code symmetry, but it's not strictly necessary.