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
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