如何使用Postgresql验证Rails中的重叠时间 [英] How to validate overlapping times in Rails with postgresql
问题描述
我有一个事件模型，该模型具有 start_at 时间和 end_at 我的日程安排应用程序中的时间，我想在保存之前验证重叠时间。
我在 Cloud9 上创建我的rails应用程序。 / p>
我的查看图像如下；
第1天
07 ：00-07:20 event1
10:30-11:30 event2
15:40-16:10 event3
[添加事件按钮]
Day2
08:15-09:05 event4
12:08-13:04 event5
14:00-14:25 event6
[添加事件按钮]
[保存时间表按钮]
start_at 时间和 end_at 时间可以更改并同时添加。
我想做的是如果我尝试为 Day1 添加（或更改为） 07:05-07:30 以显示错误例如， Day2 的 13:50-14:30 ，依此类推。
例如；
app_development = ＃从事件中选择*；
id | start_at | end_at |标题|细节| 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
我添加了 07:05 -上表07:30 ，但验证无效。
尽管我问了类似的问题，建议我使用postgresql而不是sqlite3。
所以我设法配置了postgresql，但是结果是一样的。
如果可以给我如何检查和显示错误的信息，将不胜感激。
schema.rb
create_table events，强制：：cascade do | t |
t.time start_at
t.time end_at
t.string title
t.integer room_id
...
create_table rooms，强制：：cascade do | t |
t.string房间
t.integer schedule_id
...
create_table schedules，强制：：cascade do | t |
t.string title
t.integer user_id
t.date departure_date
...
给出以下模型：
class事件< ActiveRecord :: Base
属于：room，inverse_of：：events
has_one：schedule，autosave：false，through：：room
...
validate：cannot_overlap_another_event
def not_overlap_another_event
范围= Range.new start_at，end_at
重叠= Event.exclude_self（id）.in_range（范围）
重叠错误除非overlays.empty？
结束
范围：in_range，-> range {
where（’（（start_at BETWEEN？AND？）'，range.first，range.last）
}
scope：exclude_self，-> id {where.not（id：id）}
def overlay_error
errors.add（：overlap_error，'这个小时已经安排了一个活动！'）
结尾
班级时间表< ActiveRecord :: Base
属于：user
has_many：rooms，inverse_of：：schedule
accepts_nested_attributes_for：rooms，allow_destroy：true
...
类房间< ActiveRecord :: Base
属于：schedule，inverse_of：：rooms
has_many：events，inverse_of：：room
accepts_nested_attributes_for：events，allow_destroy：true
...
_schedule_form.html.erb
<％=渲染'shared / error_messages'，object：f.object％>
<％= f.label：title％>
<％= f.text_field：title，类： form-control％>
< br>
<％= f.label：出发日期％>
< div class = input-group date id = datetimepicker>
<％= f.text_field：出发日期，：value => （如果是f.object.departure_date，则为f.object.departure_date），类： 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 | a | ％>
< div id = room_<％= a.object.object_id％>>
< p class = day-number-element-selector< b> Day& nbsp;<％= a.index.to_i + 1％< / b< / &
<％= a.simple_fields_for：events do | e | ％>
< span class = form-inline>
< p>
<％= e.input：start_at，标签：false％>& nbsp;& nbsp;-& nbsp;& nbsp;& nbsp;
<％= e.input：end_at，标签：假％>
< / p>
< / span>
<％= e.input：title，标签：false％>
<％end％>
< / div>
<％= a.link_to_add添加事件，：events，数据：{target： #room _＃{a.object.object_id}}，类： btn btn-primary ％>
<％= a.input：room％>
<％end％>
< / div>
如果能给我如何检查和显示错误的信息，将不胜感激。
编辑
编辑如下；
event.rb
scope：in_range，-> range {
where（'（（start_at BETWEEN？AND？或end_at BETWEEN？AND？）OR（（start_at< =？AND end_at> =？））'，range.first，range.last，range.first， range.last，range.first，range.last）
}
尽管工作，当我在 id = 8 之后的另一天添加事件时，此验证不起作用。 （请参见 created_at 和 updated_at ）
app_development =＃从事件中选择*；
id | start_at | end_at |标题|细节| 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
您的
scope：in_range，->范围{
where（'（（start_at BETWEEN？AND？）'，range.first，range.last）
}
在您的示例中，您最终检查了7:05和7:30之间的 start_at ，但 start_at < /第1天的/ code>是 7:00 ，超出了该范围。
有您需要处理以下四种情况：
新的范围重叠开始
现有：|- ----------- |
新：| ------- |
新范围重叠结束
现有：| ------------ |
新：| ------- |
现有范围内的新范围
现有的：| ------------ |
新：| ------- |
新范围内的现有范围
现有：| ------- |
新：| ------------ |
看，您可以看到前三种情况是通过检查
$ b来处理的$ b
new_start在start_at和end_at
之间或
new_end在BETWEEN start_at和end_at
之间 然后，您只需要通过添加
或
start_at在new_start和new_end
$ b $之间b
您可以在 end_at 上添加类似的支票以实现代码对称性，但这并非绝对必要。
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 <%= 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 %> -
<%= 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
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.
这篇关于如何使用Postgresql验证Rails中的重叠时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
