How to validate range dates with rails 6

Question

hello i have a post module where the user can select the dates for publishing his posts, i have 2 fields

start_date:date and expiration_date:date 

i want to make a validation like this

if user selects start_date = 2022-10-14 and expiration_date = 2022-10-22, he can't create another post that is between that range.

because i can have only 1 post published at a time, so with this validation i will force the user to select any other date that is not in between this selected range dates.

Answer 1

Just check that there is no other post that starts before the expiration date and ends after the start date. Also exclude this post's id in your check in case you're updating an existing post. (The post shouldn't prevent itself from changing).

This will catch posts that overlap the current post completely or partially, or that start and end within the current post.

validates :date_range

def date_range
  if user.posts.where.not(id: id).where(start_date: ..expiration_date, expiration_date: start_date..).any?
    errors.add(:start_date, 'there is already a post that overlaps these dates')
  end
end

Answer 2

Inside you Post model you'll need a some validation.

You can create a custom method that will check whether the post you're trying to create has a start date between any current post.

class Post < ApplicationRecord
  
  validate :post_exists? on: :create


  private

  def post_exists?
    return unless Post.where("? BETWEEN start_date AND expiration_date", self.start_date).present?
  
    errors.add(:start_date, "Post already exists")
  end

end

I'm unable to test this at the moment, but its roughly what you'll need.

Some light reading on the on the subject ~
Custom validate methods: https://guides.rubyonrails.org/active_record_validations.html#custom-methods
Validating on certain actions, i.e. on: :create: in this case. https://guides.rubyonrails.org/active_record_validations.html#on

It's worth mentioning there are some very similar other questions on stack, worth a google. Rails ActiveRecord date between

Answer 3

So if I understand correctly you are looking to ensure there are no "overlaps". This entails ensuring all of the following are true

• New start_date is not BETWEEN (inclusive) an existing start_date and expiration_date
• New expiration_date is not BETWEEN (inclusive) an existing start_date and expiration_date
• New start_date is not prior to an existing start_date and New expiration_date is not after the corresponding existing expiration_date

To satisfy these rules I would implement as follows:

class Post < ApplicationRecord
  validates :start_date, presence: true,comparison: { less_than: :expiration_date}
  validates :expiration_date, presence: true, comparison: { greater_than: :start_date }
  validate :non_overlapping_date_range

  def time_frame 
    start_date..expiration_date
  end 
  
  private 
    def non_overlapping_date_range 
       overlaps_post = Period
         .where(start_date: time_frame )
         .or(Post.where(expiration_date: time_frame ))
         .or(Post.where(start_date: start_date..., expiration_date: ...end_date))
         .where.not(id: id)
         .exists?
      errors.add(:base, "overlaps another Post") if overlaps_post
   end 
end

This will result in the following query

 SELECT 1 AS one 
 FROM 
  posts 
 WHERE
  ((posts.start_date BETWEEN '####-##-##' AND '####-##-##' 
  OR posts.expiration_date BETWEEN '####-##-##' AND '####-##-##')
  OR (
    posts.start_date > '####-##-##' 
    AND posts.expiration_date < '####-##-##'
  ))
  AND posts.id != # -- IS NOT NULL on create
 LIMIT 1

Using OVERLAPS (Postgres)

Postgres offers a function called OVERLAPS however the this does not fully fit the desired situation because this function treats end_date == new_start_date as continuous rather than overlapping. To counteract this we need to adjust the start_date and end_date for the purposes of the query

This can be achieved as follows:

def non_overlapping_date_range 
  overlaps_post = Post.where.not(id: id)
                 .where('(?,?) OVERLAPS (posts.start_date, posts.expiration_date)',
                        start_date - 1, end_date + 1))
                 .exists?
errors.add(:base, "overlaps another Post") if overlaps_post

end

SQL:

SELECT 1 AS one 
 FROM 
  posts 
 WHERE
  ('####-##-##','####-##-##') OVERLAPS (posts.start_date,posts.expiration_date)
  AND posts.id != # -- IS NOT NULL on create
LIMIT 1

Arel port of the same:

def non_overlapping_date_range 
  left = Arel::Nodes::Grouping.new(
    [Arel::Nodes::UnaryOperation.new(
      'DATE', 
      [Arel::Nodes.build_quoted(start_date - 1)]),
    Arel::Nodes::UnaryOperation.new(
      'DATE', 
      [Arel::Nodes.build_quoted(expiration_date + 1)])])
  right = Arel::Nodes::Grouping.new(
    [Post.arel_table[:start_date],
     Post.arel_table[:expiration_date]])
  condition = Arel::Nodes::InfixOperation.new('OVERLAPS', left, right)
  errors.add(:base, "overlaps another Post") if Post.where.not(id: id).where(condition).exists?
end