Validate existing records with overlap item ranges taking nil as infinite-CodePudding

I'm here because I've been looking around for some queries that could help to find existing records with overlapping item ranges but I couldn't find anything. Let's say I have a model named Cart. A Cart has the following attributes: item_min and item_max where item_max is nullable and will be taken as infinite when nil. In my model, I want to add a validation so no records with overlapping item ranges could be saved.

I have created a query but it's not working for all my test cases:

saved cart: item_min: 2, item_max: nil
try to save cart: `item_min: 1, item_max: 1` VALID
try to save cart: `item_min: 1, item_max: 2` VALID
try to save cart: `item_min: 1, item_max: 6` INVALID
try to save cart: `item_min: 4, item_max: 7` INVALID
try to save cart: `item_cart: 4, item_max: nil` INVALID

saved cart: `item_min: 2, item_max: 7`
try to save `cart: item_min: 1, item_max: 1` VALID
try to save cart: `item_min: 8, item_max: 10` VALID
try to save cart: `item_min: 8, item_max: nil` VALID
try to save cart: `item_min: 1, item_max: 2` INVALID
try to save cart: `item_min: 1, item_max: 8` INVALID
try to save cart: `item_min: 1, item_max: 5` INVALID
try to save cart: `item_min: 5, item_max: 10` INVALID
try to save cart: `item_min: 3, item_max: 5` INVALID
try to save cart: `item_min: 1, item_max: nil` INVALID

I created the following query:


  def validate_item_count_range
    if item_count_max.nil?
      overlap_carts = Cart.where(item_count_max: nil)
    else
      overlap_carts = Cart.where(
        "item_count_min >= ? AND item_count_max <= ?", item_count_min, item_count_max,
      ).or(
        Cart.where(
          "item_count_min <= ? AND item_count_max IS NULL", item_count_min,
        ),
      )
    end

    errors.add(:item_count_min, "overlaps with existing carts") if overlap_carts.present?
  end

However, this validation is not working for all my test cases. Could you please help me to improve my query so mi test cases could pass?

BTW, I'm using postgresql

CodePudding user response：

Using Range#overlaps?, ActiveRecord::Calculations#pluck and Array#any?

Without special SQL query

if Cart.pluck(:item_min, :item_max).any? { |min, max| (min..max).overlaps?(item_min..item_max) }
  errors.add(:base, :overlaps_with_existing_carts)
end

An endless range has a definite begin value, but a nil end value. You can omit this nil

(8..nil) == (8..)
# => true

Such a range includes all values from the begin value

(8..nil).overlaps?(4..6)
# => false

(8..nil).overlaps?(4..9)
# => true

And of course this method works with usual ranges

(4..6).overlaps?(6..8)
# => true

(4..6).overlaps?(1..3)
# => false