Making a reservation app. How can I check or validate the date for example date_start and date_end has the value of
date_start date_end 26-11-2017 27-11-2017 I will create another reservation which has these starting and end date values but it conflicts with the other values. How can I check for conflicting dates?
date_start date_end 25-11-2017 28-11-2017 Used this to validate the dates in between
Model
validate :no_reservation_overlap scope :overlapping, ->(period_start, period_end) do where "((date_start <= ?) and (date_end >= ?))", period_end, period_start end private def no_reservation_overlap if (Reservation.overlapping(date_start, date_end).any?) errors.add(:date_end, 'it overlaps another reservation') end end View - choosing the date
<%= f.label :'date_start:' %> <%= f.date_field :date_start %> <%= f.label :'date_end:' %> <%= f.date_field :date_end %> Sample dates 26 to 27 are already booked/reserved supposedly the it must prevent from inserting 25 to 28 because 26 to 27 are already booked.
3 Answers
Answers 1
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.
Answers 2
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 Answers 3
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 
0 comments:
Post a Comment