Validate existing records with overlap item ranges taking nil as infinite

Question

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

Answer 1

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

As Jad wrote in comment, the performance of such validation with arrays will be low if there are million records. The idea for SQL query using built-in ranges in PostgreSQL:

if Cart.exists?(["numrange(item_count_min, item_count_max, '[]') && numrange(?, ?, '[]')", item_count_min, item_count_max])
  errors.add(:base, :overlaps_with_existing_carts)
end

RDBMS will optimize such query. It will be much more effective than operate with giant array

[] in this query means inclusive lower and upper bounds (by default upper bound is exclusive)

Using NULL means that the range is unbounded

&& operator checks overlaps

SELECT numrange(10, NULL, '[]') && numrange(20, 40, '[]');
-- ?column? 
-- ----------
--  t

SELECT numrange(10, 20, '[]') && numrange(20, 40, '[]');
-- ?column? 
-- ----------
--  t