nil を無限とみなし、項目範囲が重複している既存のレコードを検証します。

概要

ここに来たのは、重複する項目範囲を持つ既存のレコードを検索するのに役立つクエリをいくつか探していたのですが、何も見つかりませんでした。 Cart という名前のモデルがあるとします。 Cart には次の属性があります: item_min および item_max。item_max は null 可能で、nil の場合は無限とみなされます。私のモデルでは、項目範囲が重複するレコードが保存されないように検証を追加したいと考えています。

クエリを作成しましたが、すべてのテストケースで機能しません。

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

次のクエリを作成しました。


  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

ただし、この検証はすべてのテストケースで機能するわけではありません。私のテストケースが合格できるようにクエリを改善するのを手伝ってもらえませんか?

ところで、私はpostgresqlを使用しています

解決策

Range#overlaps?、ActiveRecord::Calculations#pluck、Array#any? を使用します。

特別な SQL クエリを使用しない場合

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

無限範囲には明確な開始値がありますが、終了値はありません。この nil は省略できます

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

このような範囲には、開始値からのすべての値が含まれます。

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

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

そしてもちろん、この方法は通常の範囲で機能します

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

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

Jad がコメントに書いたように、レコードが 100 万件ある場合、配列を使用したこのような検証のパフォーマンスは低くなります。 PostgreSQL の組み込み範囲を使用した SQL クエリのアイデア:

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 はそのようなクエリを最適化します。巨大なアレイで運用するよりもはるかに効率的です

このクエリの [] は、下限と上限を含むことを意味します (デフォルトでは、上限は排他的です)。

NULL を使用すると、範囲に制限がないことを意味します

&& 演算子は重複をチェックします

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

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