如何使用Postgresql验证Rails中的重叠时间

本文介绍了如何使用Postgresql验证Rails中的重叠时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个事件模型，该模型具有 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 是 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&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

解决方案

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.